Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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?
Wow, you are amazing. Thank you very much!
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
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
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.
Ah, I see. Here is a Dropbox link:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |