DAX for highlighting TopN values in a drilled down bar chart

[Anonymous]

Hi all, 

I am looking to visualize data that is drilled down to hours per days in a bar chart. I want to visualize the topN maximum values of this bar chart. I have a date table storing the dates and days and I have a timetable that stores the hours. These tables are linked through my main data table. The DAX formula I tried to calculate the topN is as follows: 

TestTop = 
VAR SelectedTop = 3
RETURN
SWITCH(TRUE(),
    SelectedTop = 0, [Utilization],
    RANKX ( 
            CROSSJOIN(DISTINCT(DateTable[Day]), DISTINCT(TimeTable15min[Hour])), 
            [Utilization],
                )
                  <= SelectedTop,
        [Utilization]
)

As you see in the figure below, all the bars are highlighted. I feel the RANKX is applied to every bin of the bar chart separately. Therefore, all the bins are highlighted.

Thanks in advance

[wdx223_Daniel]

@Anonymous 

[Anonymous]

Hi, 

Thank you very much! It is a great step in the right direction, but not the result I really wanted. Here is a more clear example of what I want to see with a top 3. 

Would the current solution be easily adjustable to get this result? 

[wdx223_Daniel · Accepted solution]

[Anonymous]

Wow, you are amazing. Thank you very much!

[dedelman_clng]

Hi @Anonymous  - 

When you create the crossjoin inside your measure, each DISTINCT works in the current filter context, which in your case is a specific DOW and time bucket. Try using ALL around the date tables and that should expand the context to your entire dataset.

DAX also has a TOPN function if that might be easier. Details here: https://dax.guide/topn/ 

If this doesn't help, please provide data in a format that can be copy/pasted and the code for [Utilization], or share a link to your PBIX file with sensitive data removed.

Hope this helps

David

[Anonymous]

Hi David, 

Thanks for your response. Unfortunately, I have not been able to fix the issue the way you described it. Could you take a look at my PBIX file? Here is the link: https://app.powerbi.com/groups/me/reports/f13a3b88-da7a-49a0-8e41-c28f480c53ba?ctid=76a2ae5a-9f00-4f... 

Thanks

[dedelman_clng]

Hi @Anonymous - the link you provided doesn't work. I will need the pbix (Power BI Desktop) file, not the report published in the Power BI service.  People usually use Dropbox or Google Drive or OneDrive to provide a copy of the file.

[Anonymous]

Ah, I see. Here is a Dropbox link: 

[dedelman_clng]

HI @Anonymous  - sorry, I have not been able to make any headway on your issue. I'm going to reach out and see if others can help

@Greg_Deckler 

@AntrikshSharma 

@vanessafvg 

@PaulDBrown