topic Re: Need Help to apply sorting in dynamic created column and rows header in DAX Commands and Tips

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

Anonymous — Sat, 08 Aug 2020 16:05:27 GMT

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.

Here is the link of the .pbix file for reference
https://www.dropbox.com/s/cjmt597dba3dsf5/HeathCare%20-%20Seasonality%20Analysis.pbix?dl=0

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

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

Greg_Deckler — Sat, 08 Aug 2020 14:49:37 GMT

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

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

Anonymous — Sat, 08 Aug 2020 15:02:38 GMT

Hi Greg_Deckler ,

I've updated my question with a good explanation. Can you please look into it again

Thanks

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

Greg_Deckler — Sat, 08 Aug 2020 15:28:49 GMT

Anonymous - 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)

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

Anonymous — Sat, 08 Aug 2020 16:08:03 GMT

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

Not sure about your DAX is for sorting headers or something else?

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

Fowmy — Sat, 08 Aug 2020 17:42:51 GMT

Anonymous

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

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

Greg_Deckler — Sat, 08 Aug 2020 18:08:27 GMT

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

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

Anonymous — Sun, 09 Aug 2020 05:53:27 GMT

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

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

Anonymous — Sun, 09 Aug 2020 06:02:33 GMT

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

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

Greg_Deckler — Sun, 09 Aug 2020 11:42:01 GMT

Anonymous - 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>

)

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

Anonymous — Mon, 17 Aug 2020 13:05:29 GMT

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?

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

Fowmy — Mon, 17 Aug 2020 13:07:38 GMT

Anonymous

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