Matrix Table configuration problem

[Clint]

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

[MFelix]

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]

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 · Accepted solution]

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

[Clint]

Thank you Miguel - That did the trick.  Much appreciated.