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 worth mentioning that I have a date filter on the page to show Debt as at last week as Debt is a point in time measure.

The issue I am having is when I add a field from my dimension table the table does not behave as expected.

Below is my relationships where Centre Code and Date from Debt Detail are joined to the respective fields in Calendar and Centres.

 

 

Below is my measures for Debt & Total Debt.

 

Debt = 
CALCULATE (
    SUM ( 'Debt Detail'[Debt Amount] ),
    'Debt Ages'[Debt Age] <> "Prepaid"
)

Total Debt = 
CALCULATE (
    [Debt],
    FILTER ( 'Debt Detail' , 'Debt Detail'[Date] = MAX ( 'Debt Detail'[Date] ) )
)

 

As you can see from the below table when I have Account ID and Total Debt there using the TOP20 on Account ID it appears to be correct.

 

When I add Centre Code from the Centres dimension table the data goes very strange

 

 

Why would this be happening?

2 Replies

  • 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