cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adeeln
Post Patron
Post Patron

Need Help to apply sorting in dynamic created column and rows header

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.

 

adeeln_0-1596895735223.png

 

 

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.

 
 
Last thing I also want to hide empty/zero rows or column values for that I have applied greater than zero checks on visual level filters but it's not working. Please help
Thanks
1 ACCEPTED 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 🙂

YouTube, LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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>

  )

)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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 🙂

YouTube, LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy 

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors