cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clint
Helper V
Helper V

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
matrix table problem.jpg

 

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors