how to order the matrix row header based on value of a column with switch statement

oakfootballclub — Fri, 26 Jul 2024 02:56:44 GMT

Hi experts, I created a two hierarchical structure column header based on dimension table like this:

id	type	rank
2	Entertainment	10
10	Telecommunication	90
1	Airfare	90
5	Hotel Accommodation	50
6	Meal	20
12	Miscellaneous	90
7	Meal Allowance	21
15	Cash Advance	90
4	Ground Transportation	40
11	External Course	90
3	Gift	30
8	Other Traveling Expense	90
9	Per Diem Allowance	90
14	Medical check	90
16	Total Amount	2
18	Base Currency Amount	1
17	Base Currency	110

to get the correspond value I created a measure:

cal_amount_ =
SWITCH(TRUE(),
SELECTEDVALUE('dim'[id]) =1,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="1") +0,
SELECTEDVALUE('dim'[id]) =2,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="2") +0,
SELECTEDVALUE('dim'[id]) =3,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="3") +0,
SELECTEDVALUE('dim'[id]) =4,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="4") +0,
SELECTEDVALUE('dim'[id]) =5,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="5") +0,
SELECTEDVALUE('dim'[id]) =6,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="6") +0,
SELECTEDVALUE('dim'[id]) =7,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="7") +0,
SELECTEDVALUE('dim'[id]) =8,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="8") +0,
SELECTEDVALUE('dim'[id]) =9,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="9") +0,
SELECTEDVALUE('dim'[id]) =10,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="10") +0,
SELECTEDVALUE('dim'[id]) =11,
CALCULATE(SUM(fact[amount]),fact[expense_type] ="11") +0,
SELECTEDVALUE('dim'[id]) =12,

....

and then the I put the type column into matrix column:

the problem is that I want to sort the id by total amount column value,

do you have solution to help me resolve it? Thank you.

Re: how to order the matrix row header based on value of a column with switch statement

Anonymous — Fri, 26 Jul 2024 07:46:05 GMT

Hi oakfootballclub ,

I created a similar form to accomplish what you want to accomplish.

Then I create a matrix.

If you want to change the matrix row header based on value of a column, I think you can take the steps below.

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Re: how to order the matrix row header based on value of a column with switch statement in DAX Commands and Tips

how to order the matrix row header based on value of a column with switch statement

Re: how to order the matrix row header based on value of a column with switch statement