Forum Discussion

Clint's avatar
Clint
Helper V
3 years ago
Solved

Matrix Table configuration problem

Hi All,
Having a problem getting a matrix table structured the way I want it. I need to combine data from two tables into something like the embedded pic. The matrix table is structured so the Program name (projects table) is the 1st level of the hierarchy and each exception (exceptions table). What I want is, program related data only at the program level and exception data only at the exception level.
I tried using in scope to do this for the Program - starting w/program description but it didn't work and repeated the field values for each exception. Would appreciate some insights into how to isolate Program data so it only shows on the top line.
Ex. mockup

 

Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    Hi Clint ,

     

    Try to make the following change, however without any data is difficult to pin point the correct syntax

     

     

    Project Desc in Scope =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Exceptions Reporting'[Description] ), BLANK (),
    ISINSCOPE ( 'Projects'[Project Name] ), MAX ( 'Projects'[Project Description] )
)

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi Clint ,

     

    Based on what you refer you may need a ISINSCOPE has you refered however this needs to be calculated in the correct way since if you do a isinscope for the program name that is the parent that will return values in all lines.

     

    Another option can be the HASONEVALUE for the exception.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • Clint's avatar
      Clint
      Helper V
      3 years ago

      Hi Miguel,

       

      The image I attached is a mock-up of what the report should look like.  Here is the measure I tried to use to get the Program related data to show only at the Program level:

      Project Desc in Scope =
      CALCULATE (
          IF(ISINSCOPE('Projects'[Project Name]),
              MAX('Projects'[Project Description]),
          IF(ISINSCOPE('Projects'[Project Name]) && IF(ISINSCOPE('Exceptions Reporting'[Description]),""),"")))
      Unfortunately, this doesn't work.
      I think I understand why it doesn't work but I don't know how to write the dax to make it work correctly
      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        Hi Clint ,

         

        Try to make the following change, however without any data is difficult to pin point the correct syntax

         

         

        Project Desc in Scope =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Exceptions Reporting'[Description] ), BLANK (),
    ISINSCOPE ( 'Projects'[Project Name] ), MAX ( 'Projects'[Project Description] )
)