Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey Expert,
Here I'm with a challenging part with Power BI.
I'm creating a report similar to the tableau. Analysis-Seasonality: Link
I've been created development-related work in power bi similar to this tableau dashboard. but unfortunately, in the end, I'm stuck on the dynamic header sorting.
I need help to sort the matrix's dynamic column and rows header. Dynamic means based on the slicer selection, column, and rows header get changed. As in the below image.
In the above image, I have a dynamic slicer year * week, year * quarter, etc. based on this slicer selection header gets change.
Let's say year * week is selected, then the year will be displayed on row header, and week will be on the column header.
so help need in, If I click on Year * Week year row header and column header should be in sorted form ascending or descending whatever. Year (2015, 2016, 2017, etc.) in row header & Weeks (w1, w2, w3, and so on) in columns
Similarly when users click on other options like Month * Hour from slicer then both row header and column header should be in sorted form like Month (Jan, Feb, Mar, etc.) & Hour (0, 1, 2, 3, 4, 5, etc.). That's thing is not getting sort out by me.
Please help how can I sort this matrix.
Solved! Go to Solution.
@adeeln
If you need your soring to work right, your weeks should be like W01, W02 . . . .W10, so on.
Change it from W1 to W01 and the same up to W9.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@adeeln - Hard to follow. You might need the Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Hi @Greg_Deckler ,
I've updated my question with a good explanation. Can you please look into it again
Thanks
@adeeln - I'm either missing it or it is still not clear. You say you want to dynamically sort the matrix but I don't see where you specify how you want the matrix sorted. For the 0 stuff, just implement a meausure that works like:
Measure =
VAR __Calc = <Some Calculation>
RETURN
IF(__Calc <= 0,BLANK(),__Calc)
I just cleaned the description for better understanding, let me explain with an example.
If I click on Year * Week year row header and column header should be in sorted form ascending or descending whatever. Year (2015, 2016, 2017, etc.) in row header & Weeks (w1, w2, w3, and so on) in columns
Similarly when users click on other options like Month * Hour from slicer then both row header and column header should be in sorted form like Month (Jan, Feb, Mar, etc.) & Hour (0, 1, 2, 3, 4, 5, etc.). That's thing is not getting sort out by me.
Not sure about your DAX is for sorting headers or something else?
@adeeln - My DAX was for removing 0 values and instead return blank.
So, couple things.
1. I implemented my DAX in your measure so that zeros are not displayed (updated PBIX attached below sig)
2. You will need to implement a Sort By column on your Quarters column and potentially your Values column
Attached below sig is the updated PBIX file with minor changes. I did not implement sort by columns.
You've resolved my 2nd problem 🙂 Nice.
Regarding sorting,
you are right we have to write some calculated column like sort by. But I really confuse how it would be possible since I've multiple things in columns & rows like (Year number, Month Name, Week Name, Hour, quarter number, etc).
How I'll create sort column which will decide to sort based on slicer selection.
Thanks
@adeeln - Well, worst case scenario, you have one massive SWITCH(TRUE()...) statement. So basically:
ADDCOLUMNS(
<table>,
"Sort",
SWITCH(TRUE(),
[Column] = "Jan",1,
[Column] = "Feb",2,
[Column] = "Mar",3,
[Column] = "Q1",1,
[Column] = "Q2",2,
[Column] = "W1",1,
[Column] = "W2",2,
[Column] = "W3",3,
<etc>
)
)
@adeeln
If you need your soring to work right, your weeks should be like W01, W02 . . . .W10, so on.
Change it from W1 to W01 and the same up to W9.
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy ,
This really works 😄
I've recreated the matrix to remove any sorting applied on the table.
then I've tried to apply weeks number like this... this is working and automatically applying header sort my goodness. It may built-in features. I've created Hour column like H01, H02, H03...H24 so on,
then created Week number column like W01, W02, W03 ... W52 so on,
then created Month number column like M01, M02, M03 ... M12 so on,
then created Day number of month column like D01, D02, D03...D31 So on.
all the above column headers are showing me in the sorted form that's great and really happy to see this feature.
the only issue is with a day of year D01, D02, D03,...D365 is not getting sorted in the right sequence.
What to do with it?
@adeeln
Try D001, D002...D365
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Ok, I can convert it to W01 but will it automatically sort headers?
PBIX is attached please apply your changes and show me sorting on headers.
Thanks
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |