topic Re: Sort each column in matrix independences other columns in DAX Commands and Tips

Sort each column in matrix independences other columns

Someone22 — Wed, 18 Jun 2025 11:52:04 GMT

Hello,

Could you please support me?

I have a matrix in Power Bi, which is show the changes of materials.

Currently i have 6 months, July, August, September, October, November, December. The months are dynamic, every cases(date of update) includes 6 months, the intervall change in every next month, so in July the first month will be 2025.August and the last months will be 2026.January. Month is a date format.

So, what i would like to do: sort any column independent the others

for example:

I want to see the TOP increasing in "2025. szeptember" column independent the other columns, but i can not sort any month column, only just the Total column. So, the result what i would like to see is, if i sort descending "2025. szeptember" column, in that column see the decreasing list from the biggest value to the lowest, but in the other column i just see the changes which are not sorted.

For example with values:

Material 2025.július 2025.augusztus 2025.szeptember 2025.október 2025.november 2025.december

A 100 200 800 150 130 300

B -50 -100 750 50 80 900

C 600 120 700 90 170 40

Is that possible to do that? It have to be dynamic, the intervall of month change in every next month.

Thank you in advance!

Re: Sort each column in matrix independences other columns

Rupak_bi — Wed, 18 Jun 2025 13:14:57 GMT

Hi Someone22 ,

In matrix, you cannot sort columns. But in table, you can. What you need to do....

1. Make 6 different measure. name like month 1, month 2 ......month 6.

2. the dax will be like

month1 = calculate(sum(table,value), table,month = month(today())+1)

month1 = calculate(sum(table,value), table,month = month(today())+2)...........like this

the drawback will be the month name cannot be seen.

Re: Sort each column in matrix independences other columns

mark_endicott — Wed, 18 Jun 2025 13:22:26 GMT

Someone22 - the only way to acheive this would be to create a measure for each month, but then your matrix will not be dynamic.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

Re: Sort each column in matrix independences other columns

v-achippa — Mon, 23 Jun 2025 06:57:31 GMT

Hi Someone22,

Thank you for reaching out to Microsoft Fabric Community.

Thank you mark_endicott and Rupak_bi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.
If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: Sort each column in matrix independences other columns

Someone22 — Mon, 23 Jun 2025 08:31:04 GMT

Hello mark_endicott ,

I tried it with measures and yes, it is not working in matrix, but i need matrix, so i will do it in separate matzrix.

Thank you!

Re: Sort each column in matrix independences other columns

Someone22 — Mon, 23 Jun 2025 09:06:59 GMT

Hello Rupak_bi ,

Sorry for the late answer, but i tried to do it in several ways because i already have a measure for the changes.

So if i create a measure for the months after that i have to create a measure of measures for the changes of between of two date of demands.

The dax what i use for changes: i could not implement your dax to my dax 😞

Demand Change = VAR CurrentDate = MAX('YourTable'[Date]) VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month]) VAR CurrentDemand = SUM('YourTable'[Demand]) VAR PrevDate = CALCULATE( MAX('YourTable'[Date]), FILTER( ALL('YourTable'), 'YourTable'[Date] < CurrentDate && 'YourTable'[Month] = CurrentMonth ) ) VAR PrevDemand = CALCULATE( SUM('YourTable'[Demand]), 'YourTable'[Date] = PrevDate && 'YourTable'[Month] = CurrentMonth ) RETURN IF(ISBLANK(PrevDemand), BLANK(), CurrentDemand - PrevDemand)

Maybe, you have any idea?

Thank you!

Re: Sort each column in matrix independences other columns

mark_endicott — Mon, 23 Jun 2025 10:22:19 GMT

Someone22 - thanks, if my suggestion has helped, please mark it as the solution.

Re: Sort each column in matrix independences other columns

Rupak_bi — Mon, 23 Jun 2025 10:47:47 GMT

Hi Someone22 ,

Your ask was to sort individual columns and based on the sample data you shared, I have suggested. However now it looks like you are trying to get some differential. So please share sample data which represents the exact table schema you are working with and also share the desired output. Again , please note, individual columns sorting is not possible in matrix and you need to use a table visual.

Re: Sort each column in matrix independences other columns

Someone22 — Mon, 23 Jun 2025 12:59:29 GMT

Hello Rupak_bi ,

I am really sorry if i was missunderstanding. So, here in my database and steps.

Database: it is dynamic, because the first month will be August and the last month will be 2026.January in July, and so on...

Date Material Material descr Customer Month Base unit Demand
2025.05.28 A AA Company1 2025.Július PC 212
2025.05.28 B BB Company2 2025.Július PC 545
2025.05.28 C CC Company3 2025.Július PC 1124
2025.05.28 A AA Company1 2025.Augusztus PC 1138
2025.05.28 B BB Company2 2025.Augusztus PC 1543
2025.05.28 C CC Company3 2025.Augusztus PC 897
2025.05.28 A AA Company1 2025.Szeptember PC 1601
2025.05.28 B BB Company2 2025.Szeptember PC 886
2025.05.28 C CC Company3 2025.Szeptember PC 1203
2025.05.28 A AA Company1 2025.Október PC 1067
2025.05.28 B BB Company2 2025.Október PC 978
2025.05.28 C CC Company3 2025.Október PC 1942
2025.05.28 A AA Company1 2025.November PC 1346
2025.05.28 B BB Company2 2025.November PC 920
2025.05.28 C CC Company3 2025.November PC 1094
2025.05.28 A AA Company1 2025.December PC 1671
2025.05.28 B BB Company2 2025.December PC 1925
2025.05.28 C CC Company3 2025.December PC 513
2025.06.03 A AA Company1 2025.Július PC 1489
2025.06.03 B BB Company2 2025.Július PC 1175
2025.06.03 C CC Company3 2025.Július PC 346
2025.06.03 A AA Company1 2025.Augusztus PC 117
2025.06.03 B BB Company2 2025.Augusztus PC 950
2025.06.03 C CC Company3 2025.Augusztus PC 1496
2025.06.03 A AA Company1 2025.Szeptember PC 389
2025.06.03 B BB Company2 2025.Szeptember PC 831
2025.06.03 C CC Company3 2025.Szeptember PC 189
2025.06.03 A AA Company1 2025.Október PC 1447
2025.06.03 B BB Company2 2025.Október PC 184
2025.06.03 C CC Company3 2025.Október PC 1052
2025.06.03 A AA Company1 2025.November PC 1511
2025.06.03 B BB Company2 2025.November PC 136
2025.06.03 C CC Company3 2025.November PC 1047
2025.06.03 A AA Company1 2025.December PC 684
2025.06.03 B BB Company2 2025.December PC 422
2025.06.03 C CC Company3 2025.December PC 653
2025.06.06 A AA Company1 2025.Július PC 990
2025.06.06 B BB Company2 2025.Július PC 1264
2025.06.06 C CC Company3 2025.Július PC 1123
2025.06.06 A AA Company1 2025.Augusztus PC 1425
2025.06.06 B BB Company2 2025.Augusztus PC 1153
2025.06.06 C CC Company3 2025.Augusztus PC 539
2025.06.06 A AA Company1 2025.Szeptember PC 1595
2025.06.06 B BB Company2 2025.Szeptember PC 1038
2025.06.06 C CC Company3 2025.Szeptember PC 1345
2025.06.06 A AA Company1 2025.Október PC 1327
2025.06.06 B BB Company2 2025.Október PC 911
2025.06.06 C CC Company3 2025.Október PC 684
2025.06.06 A AA Company1 2025.November PC 1827
2025.06.06 B BB Company2 2025.November PC 299
2025.06.06 C CC Company3 2025.November PC 234
2025.06.06 A AA Company1 2025.December PC 1205
2025.06.06 B BB Company2 2025.December PC 1130
2025.06.06 C CC Company3 2025.December PC 1492
2025.06.15 A AA Company1 2025.Július PC 571
2025.06.15 B BB Company2 2025.Július PC 1154
2025.06.15 C CC Company3 2025.Július PC 615
2025.06.15 A AA Company1 2025.Augusztus PC 857
2025.06.15 B BB Company2 2025.Augusztus PC 1078
2025.06.15 C CC Company3 2025.Augusztus PC 211
2025.06.15 A AA Company1 2025.Szeptember PC 1615
2025.06.15 B BB Company2 2025.Szeptember PC 1595
2025.06.15 C CC Company3 2025.Szeptember PC 1735
2025.06.15 A AA Company1 2025.Október PC 685
2025.06.15 B BB Company2 2025.Október PC 1889
2025.06.15 C CC Company3 2025.Október PC 1608
2025.06.15 A AA Company1 2025.November PC 466
2025.06.15 B BB Company2 2025.November PC 975
2025.06.15 C CC Company3 2025.November PC 1662
2025.06.15 A AA Company1 2025.December PC 363
2025.06.15 B BB Company2 2025.December PC 291
2025.06.15 C CC Company3 2025.December PC 1319
2025.06.23 A AA Company1 2025.Július PC 1163
2025.06.23 B BB Company2 2025.Július PC 1274
2025.06.23 C CC Company3 2025.Július PC 434
2025.06.23 A AA Company1 2025.Augusztus PC 1264
2025.06.23 B BB Company2 2025.Augusztus PC 1406
2025.06.23 C CC Company3 2025.Augusztus PC 1269
2025.06.23 A AA Company1 2025.Szeptember PC 571
2025.06.23 B BB Company2 2025.Szeptember PC 1415
2025.06.23 C CC Company3 2025.Szeptember PC 1152
2025.06.23 A AA Company1 2025.Október PC 1833
2025.06.23 B BB Company2 2025.Október PC 403
2025.06.23 C CC Company3 2025.Október PC 1135
2025.06.23 A AA Company1 2025.November PC 1106
2025.06.23 B BB Company2 2025.November PC 425
2025.06.23 C CC Company3 2025.November PC 442
2025.06.23 A AA Company1 2025.December PC 869
2025.06.23 B BB Company2 2025.December PC 900
2025.06.23 C CC Company3 2025.December PC 907

First: i had to create a matrix where we can see the changes between the consecutive dates. So the row is the date, the column is the month and the value is the dax.

The dax:

Second: after that i had to create a matrix where we can see the changes based on the materials, where the changes of dax calculate the changes between the two latest date. So, the row is the materials, the columns is the month and the value is the new dax.

New dax: similar the above, only the CurrentDemand VAR the difference.

Demand Change_type = VAR CurrentDate = MAX('YourTable'[Date]) VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month]) VAR Currentdemand = CALCULATE( SUM(SD1006_OoH[OoH]), SD1006_OoH[Date] = CurrentDate && SD1006_OoH[Month] = CurrentMonth ) VAR PrevDate = CALCULATE( MAX('YourTable'[Date]), FILTER( ALL('YourTable'), 'YourTable'[Date] < CurrentDate && 'YourTable'[Month] = CurrentMonth ) ) VAR PrevDemand = CALCULATE( SUM('YourTable'[Demand]), 'YourTable'[Date] = PrevDate && 'YourTable'[Month] = CurrentMonth ) RETURN CurrentDemand - PrevDemand

Third: In the end, i would like to create a matrix or table where i can sort any column independence the others. I could create it in separate matrix, where i can give a visual filter which is a custom column include index where i can choose the exact number and i can sort that one column. It is dynamic.

Future Month Index = VAR vToday = TODAY() VAR vNextMonthStart = EOMONTH(vToday, 0) + 1 VAR vRowDate = SD1006_OoH[Month] RETURN IF( vRowDate >= vNextMonthStart, (YEAR(vRowDate) - YEAR(vNextMonthStart)) * 12 + MONTH(vRowDate) - MONTH(vNextMonthStart) + 1, BLANK() )

It is almost ok, but not the target what i want. In the third step if i sort one of them matrix, i can not see that demand change of materials in the other month. I need to filter the others.

But, i need matrix or table where i sort the changes in any month column, paralell i see the changes of material in the other month. Why it is important? Because if we can see a really big increasing in the first step matrix in September, i can sort the Third step matrix/table and i can see that the increasing is one time or occur in other month(--> maybe system/report problem or the customer upload something wrong) or there is a same decreasing in the other month so it is not problem, just the customer moved it from other month to September.

I hope i can wrote it clear 🙂

Thank you!

Re: Sort each column in matrix independences other columns

v-achippa — Thu, 26 Jun 2025 07:57:39 GMT

Hi Someone22,

Thank you for the detailed explanation. Power bi matrix visuals do not support column wise sorting i.e sorting individual value columns. So it is technically not possible to sort one column and keep all other months unsorted in a single matrix.

After analysing your use case thoroughly, here is the best possible approach:

Use a Table visual instead of a matrix.
You have already created a dynamic column like Future Month Index, this will correctly limits the data to the next 6 months from the current month, keep using it as a filter or slicer.
And also you have implemented a measure that calculates Demand Change between the latest two dates for each material and month, that is what exactly needed here.
In the Table visual add Material, Month and Demand Change.

You can now sort by any month and still see the change values for that material in all months.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thanks and regards,

Anjan Kumar Chippa

Re: Sort each column in matrix independences other columns

v-achippa — Tue, 01 Jul 2025 04:35:30 GMT

Hi Someone22,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked?
If my response addressed, please mark it as "Accept as solution" and give kudos if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: Sort each column in matrix independences other columns

Someone22 — Tue, 01 Jul 2025 11:27:07 GMT

Hello v-achippa ,

I am so sorry, but i did not have time to try it because of other urgent project in my company.

I am not really understand, can you please show it via example?

Thank you!

Re: Sort each column in matrix independences other columns

v-achippa — Thu, 03 Jul 2025 06:43:48 GMT

Hi Someone22,

As discussed, it is not possible to sort individual columns in a Matrix visual. I have implemented your use case using a Table visual that meets all the requirements.

I have attached a pbix file below, please take a look and let me know if you need any further assistance.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thanks and regards,

Anjan Kumar Chippa

Re: Sort each column in matrix independences other columns

Someone22 — Thu, 03 Jul 2025 07:31:24 GMT

Hello v-achippa ,

Thank you for your answer and pbix file. I need some time to check and try it how can i use it efficiently with more than 5.000 different materials :).

Re: Sort each column in matrix independences other columns

Someone22 — Mon, 07 Jul 2025 06:45:07 GMT

Hello v-achippa,

I could check it and it will be okay. It is show the same result what we want to see.

Thank you so much for your support! 🙂