Help with the Dax Expression.

[Anonymous]

Hello guys,

I have a table and a slicer like the following:

I'm looking to write an DAX expression for the following logic :

I need to find the Maximum value of the Diff column corresponding to the "n"th distinct assignment number where "n" is the minimum value of the "req total pos" column. Another filter would be just the value "Keep".

In this example - after I select 9 in the slicer - I need to get - "15" since it corresponds to the second distinct assignment number.

Logic=

Min value in the "req total pos" column = "2"

so the "2" nd distinct value of the ass number = "901"

Max value of the Diff column of that assignment number(901)  is "15"

Looking forward to your help.

Thanks,

Anand!

[v-frfei-msft]

Hi @Anonymous ,

We can create a measure as below.

Measure 2 = 
VAR maxp =
    MAX ( 'Table'[req total pos] )
VAR kk =
    TOPN ( maxp, 'Table', 'Table'[Ass Number], ASC )
VAR c =
    CALCULATE ( MAX ( 'Table'[Ass Number] ), KEEPFILTERS ( kk ) )
RETURN
    CALCULATE ( MAX ( 'Table'[Diff] ), FILTER ( 'Table', 'Table'[Ass Number] = c ) )

For more details, please check the pbix as attached.

[Anonymous]

Thanks for the response and it works great! But as I had mentioned, I should also filter out the rows with "Elim". Where do I include that filter in your code?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Update the formula as below.

Measure 2 =
VAR maxp =
    MAX ( 'Table'[req total pos] )
VAR kk =
    TOPN (
        maxp,
        FILTER ( 'Table', 'Table'[Elim or keep] <> "Elim" ),
        'Table'[Ass Number], ASC
    )
VAR c =
    CALCULATE ( MAX ( 'Table'[Ass Number] ), KEEPFILTERS ( kk ) )
RETURN
    CALCULATE ( MAX ( 'Table'[Diff] ), FILTER ( 'Table', 'Table'[Ass Number] = c ) )

[parry2k]

@Anonymous Even you explained it very well but I'm still not sure how you get to value 15 based on your example. Can you put the example in excel sheet with couple of examples to understand it correctly so that we can provide you the solution.

[Anonymous]

Thank you for your response.

Let me explain the "15" -

I get 4 rows after selecting "9" from the slicer

Then I filter rows with just values "Keep" - I get 3 rows now.

Then I would look for the Min value in the Req total pos column - which will give me "2"

Let's keep "n=2"

Then I would look for the "nth" distinct assignment number which is 901(2nd distinct value). [1st distinct value = 900, 2nd distinct value = 901]

Then I would look for the max value in the Diff column correspondig to "901"

Hence it would be "15"

Hope this helps!

[parry2k]

@Anonymous got it, all these are column in your dataset or there are calculated measure. Just want to make sure I understood the dataset correctly.

[parry2k]

@Anonymous it helps, so basically there is no calculated Measure

[Anonymous]

yeah, No measures involved

[Anonymous]

Thanks for your response.

Elim or Keep , Req total pos , Diff -- these three columns are calculated columns.

Other three columns are from dataset.

Hope this helps

Thanks!