Forum Discussion

JP8991's avatar
JP8991
Icon for Kudo Commander rankKudo Commander
2 years ago

TOPN Not Working As Expected (Driving me nuts)

The purpose of my table is to show Debt by accounts and I would like to only display the top 20, to do this I am using the TOPN as a visual filter on the table by Account ID (from fact table), it is ...
v-jincheng-msft's avatar
v-jincheng-msft
Icon for Community Support rankCommunity Support
2 years ago

Hi JP8991 ,

 

The reason TOPN does not work as expected is that the relationship between the tables is one-to-many, so the target value cannot be obtained directly.

 

Please try Creating and using the following MEASURE:

 

CenterCode = var _max=
MAXX(
    FILTER(ALL('Debt Detail'),'Debt Detail'[Amount ID]=MAX('Debt Detail'[Amount ID])),[Date])
return
MAXX(
    FILTER(ALL('Debt Detail'),
    'Debt Detail'[Date]=_max),[Center Code])

 

 

 

 

Hope it helps!

 

Best regards,
Community Support Team_ Joseph Ji

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • JP8991's avatar
    JP8991
    Icon for Kudo Commander rankKudo Commander
    2 years ago

    v-jincheng-msft thanks so much for your help.

    Would you be able to provide the PBIX for this as you referenced [Centre Code] as a measure however I don't have this has a measure.

    Thanks,

     

    James