Forum Discussion

DimaMD's avatar
DimaMD
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

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


@Dax help
  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    2 years ago

    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.

12 Replies

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    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.

    • DimaMD's avatar
      DimaMD
      Icon for Solution Sage rankSolution Sage
      2 years ago

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • v-weiyan1-msft's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity Support
    2 years ago

    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.

    • DimaMD's avatar
      DimaMD
      Icon for Solution Sage rankSolution Sage
      2 years ago

      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