cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

Table Matrix Conundrum, Looking for ideas!

Hi Everyone,

 

I have a matrix that I am putting together and the second value in the row field(drill down) is pulling all values relating to the columns instead of the values that are not zero. The typical way that I would solve this is that with a column that is the "main column" over the rest, I would visual filter for values >= 1, but in this case, there are some zeros that I need. The initial row is Month and the second would be ID numbers. Below is some pictures of what I am speaking about. In the first picture, I want only the values in the "transplant intent recoveries" that equal 1. My issue is what you will see in the second picture as I will need to show if none were done at the month level. 

 

Any ideas on how I could do this?

 

dbrandone_0-1631718825400.png

dbrandone_1-1631718876990.png

 

 

2 ACCEPTED SOLUTIONS

@dbrandone Try:

Transplant Intent Recoveries Total = 
    VAR TIRtotal = 
        CALCULATE(
            COUNTROWS(v_EyeRecovery_EyeTissueRecovered),
            v_EyeRecovery_EyeTissueRecovered[RecoveryIntent] = "Transplant",
            v_EyeRecovery_EyeTissueRecovered[ParentId] = BLANK()
    )

    VAR Result = IF(
                    ISBLANK(TIRtotal),
                    0,
                    TIRtotal
    )

    Return IF(ISINSCOPE('Dates'[MonthName]),1,Result)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@dbrandone Did you add that measure to the matrix? If not, try that and turn off word wrap and then shrink the column to nothing.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@dbrandone You will want to add ISINSCOPE to your selector measure so that you always return 1 when at the Month Name level. Otherewise, seems like you are familiar with what I call a Complex Selector: The Complex Selector - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Appreciate the response @Greg_Deckler. I haven't messed with ISINSCOPE yet and just read the DAX guide on it and trying to visualize how to implement it. Below is the measure for Transplant Recoveries, and if I am understanding what you are saying, I need to tie ISINSCOPE with MonthName inside of the below measure, correct?

Transplant Intent Recoveries Total = 
    VAR TIRtotal = 
        CALCULATE(
            COUNTROWS(v_EyeRecovery_EyeTissueRecovered),
            v_EyeRecovery_EyeTissueRecovered[RecoveryIntent] = "Transplant",
            v_EyeRecovery_EyeTissueRecovered[ParentId] = BLANK()
    )

    VAR Result = IF(
                    ISBLANK(TIRtotal),
                    0,
                    TIRtotal
    )

    Return Result



 

@dbrandone Try:

Transplant Intent Recoveries Total = 
    VAR TIRtotal = 
        CALCULATE(
            COUNTROWS(v_EyeRecovery_EyeTissueRecovered),
            v_EyeRecovery_EyeTissueRecovered[RecoveryIntent] = "Transplant",
            v_EyeRecovery_EyeTissueRecovered[ParentId] = BLANK()
    )

    VAR Result = IF(
                    ISBLANK(TIRtotal),
                    0,
                    TIRtotal
    )

    Return IF(ISINSCOPE('Dates'[MonthName]),1,Result)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler July still dropped off after the ISINSCOPE add to the measure.

Hi @dbrandone,

Any update for these? Did Greg_Deckler 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@dbrandone Did you add that measure to the matrix? If not, try that and turn off word wrap and then shrink the column to nothing.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!