TOPN doesn't work as a filter

[SteveIOW]

Good morning.

I have used 

WorstModTrend = TOPN ( 1 , VALUES ( Modalities[Modality] ), CALCULATE([Avg Rise (M)]) , DESC )

to identify the modality with the worst trend.

I now want to identify what that trend is. Obviously I have a table - but in terms of picking it out I need it to appear in a card.

Non-obstetric unltrasound is produced with no difficulty by the DAX I stated above.

I have tried

WorstTrend = CALCULATE([Avg Rise (M)], Modalities[Modality] = [WorstModTrend])

and it does not work at all.

I have also tried

WorstTrend = CALCULATE([Avg Rise (M)], FILTER(Modalities, [Modality] = [WorstModTrend]))

which just gives me the total trend  (29,943) - not the worst (greatest) trend which is what I need.

Is anyone able to help please?

Cheers

Steve

[amitchandak]

@SteveIOW , You should try like

WorstTrend = CALCULATE([Avg Rise (M)], Modalities[Modality] , TOPN ( 1 , VALUES ( Modalities[Modality] ), CALCULATE([Avg Rise (M)]) , DESC ))

do you need bottom means asc

WorstTrend = CALCULATE([Avg Rise (M)], Modalities[Modality] , TOPN ( 1 , VALUES ( Modalities[Modality] ), CALCULATE([Avg Rise (M)]) , asc))

refer

https://youtu.be/QIVEFp-QiOk

[SteveIOW · Accepted solution]

Think I have done it myself - by taking a completely different approach.

WorstTrend = MAXX(SUMMARIZE(Modalities, Modalities[Modality], "Rise", LinearRegTable[Avg Rise (M)]), LinearRegTable[Avg Rise (M)])

I'm sure there is a better way although this gives the right answer. If anyone can give me the another way I will be interested.

Cheers

[SteveIOW]

@amitchandak thanks for having a look at this for me.

I have tried as you have written but of course it does not provide a boolean output so it fails. I tried putting in an = instead of a comma but that also fails as below.