return max category

[Anonymous]

Does anyone know a dax expression i could use to show the max category in the screen shot below. For example, a row that shows brother for cosco,dad for walmart, and cousin for samsclub?

[edhans · Accepted solution]

See the attached file.

First I normalized your data with Power Query so it was more like a database.

Then I used the following measure:

Max Client Spending = 
VAR BigSpending = MAX(Receipts[Spending])
RETURN
CALCULATE(
    MAX(Receipts[Client]),
    FILTER(Receipts,[Max Spending] = BigSpending)
)

I also had a Max Spending measure that is referenced in the above, which is simply:

Max Spending = MAX(Receipts[Spending])

The end result is a table that looks like this, which shows the relative, their maximum spending amount, and where it was at.

The PBIX file is here.

[TeigeGao]

Hi @Anonymous ,

We can use the following DAX query:

Measure =
CALCULATE (
    MIN ( Table1[Client] ),
    FILTER (
        ALL ( Table1 ),
        Table1[data]
            = CALCULATE (
                MAX ( Table1[data] ),
                FILTER ( ALL ( Table1 ), Table1[People] = MIN ( Table1[People] ) )
            )
            && Table1[People] = MIN ( Table1[People] )
    )
)

The result will like below: 

Best Regards,

Teige