topic DAX for highlighting TopN values in a drilled down bar chart in DAX Commands and Tips

DAX for highlighting TopN values in a drilled down bar chart

Anonymous — Wed, 13 Jan 2021 12:37:20 GMT

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

Re: DAX for highlighting TopN values in a drilled down bar chart

dedelman_clng — Thu, 14 Jan 2021 13:32:06 GMT

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

Re: DAX for highlighting TopN values in a drilled down bar chart

Anonymous — Fri, 15 Jan 2021 07:53:46 GMT

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-4f6b-95ed-5d33d77c4d61

Thanks

Re: DAX for highlighting TopN values in a drilled down bar chart

dedelman_clng — Fri, 15 Jan 2021 12:17:36 GMT

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.

Re: DAX for highlighting TopN values in a drilled down bar chart

Anonymous — Thu, 21 Jan 2021 12:41:34 GMT

Ah, I see. Here is a Dropbox link:

Re: DAX for highlighting TopN values in a drilled down bar chart

dedelman_clng — Wed, 20 Jan 2021 11:27:25 GMT

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

Re: DAX for highlighting TopN values in a drilled down bar chart

wdx223_Daniel — Thu, 21 Jan 2021 04:12:02 GMT

Anonymous

Re: DAX for highlighting TopN values in a drilled down bar chart

Anonymous — Thu, 21 Jan 2021 08:11:27 GMT

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?

Re: DAX for highlighting TopN values in a drilled down bar chart

wdx223_Daniel — Thu, 21 Jan 2021 08:35:31 GMT

Re: DAX for highlighting TopN values in a drilled down bar chart

Anonymous — Thu, 21 Jan 2021 09:11:54 GMT

Wow, you are amazing. Thank you very much!