cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Solution Sage

## Hierarchy in the table matrix

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
1 ACCEPTED SOLUTION
Community Support

Hi @DimaMD ,

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

12 REPLIES 12
Community Support

Hi @DimaMD ,

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

Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Community Support

Hi @DimaMD ,

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.

Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Super User

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

Solution Sage

@lbendlin Hi, yes

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Super User

Please show the expected outcome based on the sample data you provided.

Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Super User

See attached.

Solution Sage

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Super User

Not sure I understand.  Did you check my proposal?

Solution Sage

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]
)``````

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors