DAX formula for - MAX of COUNT

Question

I have the below dataset:

using the measure:

BalanceCount := COUNT(Balances[Balance])

which gives me the result:

However, I want the Grand Total to show the maximum amount of the BalanceCount, which is 2.

Christopher Tso · Answer 1 · 2015-12-06T05:22:37.223

0

I found a solution that works for this particular case. It will not work if columns other than Account and MonthEnd are included in the filter context.

MaxBalanceCount:=
MAXX ( SUMMARIZE (
        Balances,
        Balances[Account],
        Balances[MonthEnd]
    ),
    CALCULATE ( COUNTROWS ( Balances ) )
)

edited Dec 06 '15 at 05:22

answered Dec 06 '15 at 05:08

Christopher Tso

341
5
18

There is insufficent introspection capability in DAX to define a measure to group by the fields making up the filter context in a pivot table. – greggyb Dec 06 '15 at 16:19

score 0 · Accepted Answer · answered Dec 06 '15 at 05:08

NewMeasure:=
MAXX(
    SUMMARIZE(
        FactTable
        ,FactTable[Account]
        ,FactTable[MonthEnd]
    )
    ,[BalanceCount]
)

SUMMARIZE() groups by the columns specified, and MAXX() iterates through the table specified, returning the maximum of the expression in the second argument evaluated for each row in its input table.

Since the filter context will limit the rows of the fact table, we'll only have the appropriate subsets in each column/row grand total.

DAX formula for - MAX of COUNT

2 Answers2