topic Re: Hierarchy in the table matrix in DAX Commands and Tips

Hierarchy in the table matrix

DimaMD — Mon, 05 Feb 2024 11:04:06 GMT

Hello community, I haven't asked for help in a long time, but I faced such a problem
I have several measures that I want to group in a matrix visualization.
For the hierarchy, I created a simple table.

ID	P1	P2
1	Stock balance	Stock balance
2	Receivables	R_Cashless
2	Receivables	R_Cash
3	Payables	P_Cashless
3	Payables	P_Cash

I have measures corresponding to column names P1 and P2

My expected output should look like this. When the receivables are expanded, I should see the amount that goes to Cashless and Cash

Stock balance	150
Receivables	100
R_Cashless	70
R_Cash	30
Payables	300
P_Cashless	200
P_Cash	100

I tried to implement it through SWITCH, but for some reason I don't see the Debitor's hierarchy

Re: Hierarchy in the table matrix

lbendlin — Mon, 05 Feb 2024 22:56:25 GMT

In the matrix, Values settings, did you select "show values on rows" ?

Re: Hierarchy in the table matrix

DimaMD — Tue, 06 Feb 2024 11:53:49 GMT

lbendlin Hi, yes

Re: Hierarchy in the table matrix

lbendlin — Tue, 06 Feb 2024 15:03:11 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Hierarchy in the table matrix

DimaMD — Fri, 09 Feb 2024 07:12:22 GMT

Hi, lbendlin This is my expected result, each measure is calculated separately, and should be displayed opposite each indicator in the matrix

https://drive.google.com/file/d/18ObM0bUNEqmC6hDcZgweIJXq3gyLt1oy/view?usp=sharing

Re: Hierarchy in the table matrix

v-weiyan1-msft — Mon, 19 Feb 2024 09:49:59 GMT

Hi DimaMD ,

Based on your description,
I suggest you can create a one-to-one relationship between the two tables.

Then use the following code to create Measure.

Measure = VAR _P1 = SELECTEDVALUE(Table1[P1]) RETURN SWITCH( _P1, "Stock balance", [Stock balance], "Receivables", [R_Cash] + [R_Cashless], "Payables",[P_Cash] + [P_Cashless] )

Result is as below.

In addition, I am not sure how your semantic model was created, but if the data for Table2 is as follows.

Then "Amount" field can be placed in the matrix without creating Measure. Result is as below.

You can refer to this, but still as your actual situation as a starting point.

Best Regards,
Yulia Yan

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

Re: Hierarchy in the table matrix

DimaMD — Mon, 19 Feb 2024 13:26:59 GMT

Hi v-weiyan1-msft Thanks for the solution, but in my package there should not be a connection between the tables, in my original file the names of the measures are different

Re: Hierarchy in the table matrix

lbendlin — Mon, 19 Feb 2024 16:07:37 GMT

See attached.

Re: Hierarchy in the table matrix

DimaMD — Tue, 20 Feb 2024 08:18:04 GMT

HI, lbendlin, Thanks for the solution, but in my package there should not be a connection between the tables, in my original file the names of the measures are different.
Table 2 is an example, in my actual file the measures have different names and calculations, the logic is that there is a table 1 that has a hierarchy.
i.e. Payables corresponds to measure 1, P_Cash corresponds to measure 2, P_Cashless corresponds to Measure 3, etc.
However, there is no such connection in Tables 1 and 2

Re: Hierarchy in the table matrix

lbendlin — Tue, 20 Feb 2024 14:29:56 GMT

Not sure I understand. Did you check my proposal?

Re: Hierarchy in the table matrix

DimaMD — Wed, 21 Feb 2024 12:00:19 GMT

hi lbendlin The proposed solution you provided will not work in my data model, note that I am providing an example. Let's try to explain graphically.
I calculated the measures that perform the calculations, the customer wants to see these calculations in a matrix according to the hierarchy.
That is, if there is a calculated measure "Payables" (it has a different name in my model), it must match the value of Payables in Table 1, as well as "P_Cashless" and "P_Cash" (they also have different names in my model).
As I wrote before, I tried to do it through SWITCH, but it does not give the expected result

Total = VAR _P1 = SELECTEDVALUE(Table1[P1]) VAR _P2 = SELECTEDVALUE(Table1[P2]) RETURN SWITCH( TRUE(), _P1 = "Stock balance", [Stock balance], _P1 = "Receivables", [Receivables], --The measure may have a different name _P2 = "R_Cashless", [R_Cashless], --The measure may have a different name _P2 = "R_Cash", [R_Cash], _P1 = "Payables", [Payables], _P2 = "P_Cashless", [P_Cashless], _P2 = "P_Cash", [P_Cash] )

Re: Hierarchy in the table matrix

v-weiyan1-msft — Fri, 23 Feb 2024 06:21:25 GMT

Hi DimaMD ,

Based on your additional description, please try code as below.

MEASURE = VAR _P1 = SELECTEDVALUE ( Table1[P1] ) VAR _P2 = SELECTEDVALUE ( Table1[P2] ) RETURN IF ( ISINSCOPE ( 'Table1'[P2] ), SWITCH ( _P2, "R_Cashless", [R_Cashless], "R_Cash", [R_Cash], "P_Cashless", [P_Cashless], "P_Cash", [P_Cash], "Stock balance", [Stock balance] ), SWITCH ( _P1, "Stock balance", [Stock balance], "Receivables", [Receivables], "Payables", [Payables] ) )

Result is as below.

Hope that works for you.

Best Regards,
Yulia Yan

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

Re: Hierarchy in the table matrix

DimaMD — Mon, 26 Feb 2024 09:51:55 GMT

Hi v-weiyan1-msft So this is what I was looking for, the measure worked correctly with my data, thank you very much