Forum Discussion

_matsyabrahma's avatar
_matsyabrahma
Regular Visitor
3 years ago
Solved

Disconnected table issues/ question in matrix

Hello all,

To match the excel view i had to use disconnected table in power bi matrix and i have the below issue.
with the main table I have created two disconnected tables one for row and another for column and used below measure to map.

SWITCH(
    TRUE(),
    SELECTEDVALUE(DR_Table[DR])="Last 30 days"
    && SELECTEDVALUE(DR_Columns[DR_Columns])="Deputation" , _a,
    SELECTEDVALUE(DR_Table[DR])="Last 30 days"
    && SELECTEDVALUE(DR_Columns[DR_Columns])="Exit" , _e) etc
 

 

 

Now if i use the other dimension like eg Org or SBU in the below view i am getting a blank matrix.

 

 

But if i use like below i will inside the measures.

and how can i get normal one like this as get dimensions in columns.

 

 

 

kindly help amitchandak Anonymous Ashish_Mathur MFelix Greg_Deckler 

 

i am attaching sample pbix.

 

https://drive.google.com/file/d/11JBaB3AJEcO7Aw_WJTetWVx4Y_Fo7WnM/view?usp=sharing 

Show and Tell
  • MFelix's avatar
    MFelix
    3 years ago

    Hi _matsyabrahma ,

     

    Try to add the following metric:

    Total DRSwitch = SUMX(VALUES(DR_Columns[DR_Columns]), [DR_Switch])

    Now use this metric in the place of the other one:

     

     

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi _matsyabrahma ,

     

    The question here is the use of the SELECTEDVALUE on the DR_Columns, this syntax returns a single value for the expression in this case you would get DEPUTATION or EXIT, when you go to a higher level ORG or SBU you are picking up both values so the SELECTEDVALUE is no longer valid and returns a blank.

     

    When you do a drill down until the DR_COLUMNS level you can get result even with this hierarchy.

     

    The question here is what is the result you want to achieve when you have more than one value for the DR_Column is it a SUM, and AVERAGE?

    • _matsyabrahma's avatar
      _matsyabrahma
      Regular Visitor
      3 years ago

      Hi MFelix thanks a lot for the reply and basically i want to achieve exactly like this.
      And its all the count of employees based on Joining and Exit dates.

      About SUM or Avg it should be the sum.

      if you can suggest some alternate solution i am happy to accept.

       

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        Hi _matsyabrahma ,

         

        Try to add the following metric:

        Total DRSwitch = SUMX(VALUES(DR_Columns[DR_Columns]), [DR_Switch])

        Now use this metric in the place of the other one:

         

         

  • Analmd's avatar
    Analmd
    Frequent Visitor
    6 months ago

    Hi all, with this approach, I'm not able to sort by the columns, for example, sort by 'Last 30 days'. Is there a solution to this? Many thanks