The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
117 | |
34 | |
30 | |
24 | |
21 |