Forum Discussion

Icon for Helper II rank

Helper II

4 years ago

Solved

TOPN doesn't work as a filter

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

SteveIOW
4 years ago
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

3 Replies

amitchandak
Super User
4 years ago
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
  Helper II
  4 years ago
  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.
- SteveIOW
  Helper II
  4 years ago
  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