Forum Discussion

330SMG's avatar
330SMG
Frequent Visitor
4 years ago
Solved

Issue with Rankx measure in Table

Hi I have the following Measure created.   Top5 Customers = CALCULATE([Total Orders], FILTER(VALUES(DimCustomer[FirstName]), IF(RANKX(ALL(DimCustomer[FirstName]),[Total Orders],,DESC)<=5,[Total ...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    330SMG this is the measure for top 5 first names:

     

     

    Top5 First Names = 
    CALCULATE(
        [Total Orders],
        FILTER(
            VALUES(DimCustomer[FirstName]),
            IF(RANKX(ALL(DimCustomer[FirstName]),[Total Orders],,DESC)<=5,[Total Orders],BLANK())
        )
    )

     

     


    After you created the measure you need to make sure to put the right column in the visual (In this case. [FirstName], in the previous case [CustomerKey]). That is why in both cases I get the right result with the same data you have.

     


    Showcase Report – Contoso By SpartaBI


          

SpartaBI's avatar
SpartaBI
Icon for Community Champion rankCommunity Champion
4 years ago

330SMG I tested your measure on my sample data and it's working:

Also created a 2nd version, so you can try it, but if the 1st one didn't work then good chance this won't work neither:

Top5 Customers 2 =
CALCULATE(
[Sales Amount],
KEEPFILTERS( TOPN(5, ALLSELECTED(Customer[CustomerKey]), [Sales Amount] ) )
)
Just replace my column names with yours. 
1. Is [First Name] a unique key in your customer table?
2. Maybe you don't have a proper relatioship between dim customers and the fact?



330SMG's avatar
330SMG
Frequent Visitor
4 years ago

So I changed it to the column that is the join between the tables Customer Key and same thing, it just shows the total in the visulization not the top 5.  

Oh and I tried your TopN Dax,  Same thing.  My joins all apear correct, but obviously they art not.  I am realitively new to BI and am really stumped by this.  I need to understand the mechanism on why it is not creating individual entries.  I have worked with SQL and Excel for a long time and I am a little lost on this error.

 

    • 330SMG's avatar
      330SMG
      Frequent Visitor
      4 years ago

      Ok How do I share the file?  I am really new to the world of BI.

      That report is impressive.

      • SpartaBI's avatar
        SpartaBI
        Icon for Community Champion rankCommunity Champion
        4 years ago

        330SMG you can put in something like dropbox and share the link here or put it in your onedrive / gdrive and make that file public and put the link here.

        Thanks for the compliment 🙂 Please give it a like 🙏