Table Matrix Conundrum, Looking for ideas!

[dbrandone]

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?

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

@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

[dbrandone]

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

[Greg_Deckler · Accepted solution]

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

[dbrandone]

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

[v-shex-msft]

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

[Greg_Deckler · Accepted solution]

@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.