Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
DimaMD
Solution Sage
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.

IDP1P2
1Stock balanceStock balance
2ReceivablesR_Cashless
2ReceivablesR_Cash
3PayablesP_Cashless
3PayablesP_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 balance150
Receivables100
R_Cashless70
R_Cash30
Payables300
P_Cashless200
P_Cash100

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
v-weiyan1-msft
Community Support
Community Support

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.

vweiyan1msft_0-1708669224902.png

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.

View solution in original post

12 REPLIES 12
v-weiyan1-msft
Community Support
Community Support

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.

vweiyan1msft_0-1708669224902.png

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.

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
v-weiyan1-msft
Community Support
Community Support

Hi @DimaMD ,

 

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

vweiyan1msft_0-1708335891258.png

vweiyan1msft_1-1708335898771.png

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.

vweiyan1msft_2-1708336015823.png

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

vweiyan1msft_3-1708336033814.png

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

vweiyan1msft_4-1708336055570.png

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.

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
lbendlin
Super User
Super User

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

@lbendlin Hi, yes


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

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

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

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

See attached.

lbendlin_0-1708358834526.png

 

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

Not sure I understand.  Did you check my proposal?

lbendlin_0-1708439390961.png

 

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

 



DimaMD_0-1708516421194.png

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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

Top Solution Authors