How to select a simple value from an order Table with DAX

[rauldip22]

Hello. I need to select the "Gender" with the MAX "Amount" for the "Category" = A.

In SQL it should be: SELECT Gender FROM TableName WHERE Category = "A" ORDER BY Amount.

I need this but in a DAX query.

Gender	Amount	Category	Cases
Male	6768768	A	6
Male	344	B	8
Male	44442	C	6
Male	3344	D	5
Female	7656	A	4
Female	44577	B	3
Female	334442	C	6
Female	455	D	5

Thaks!!!

[v-xjiin-msft · Accepted solution]

Hi @rauldip22,

To achieve your requirement, you can refer to following measure:

MAX Amount =
CALCULATE (
    MAX ( 'Table'[Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[Gender] ),
    FILTER ( 'Table', 'Table'[Category] = "A" )
)

Thanks,
Xi Jin.

[Ashish_Mathur]

Hi,

What result are you expecting?

[Greg_Deckler]

Perhaps:

MaxGender = CALCULATE(FIRSTNONBLANK(GendersMan[Gender],GendersMan[Gender]),FILTER(ALLEXCEPT(GendersMan,GendersMan[Category]),GendersMan[Amount]=MAX(GendersMan[Amount])))

GendersMan is my table. You can put this into a table visual with your Category or you could add an && to your filter to isolate category A specifically or you could just do it via a slicer or filter.