Forum Discussion
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 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 Did you add that measure to the matrix? If not, try that and turn off word wrap and then shrink the column to nothing.
6 Replies
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
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 Resultdbrandone 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)
