Forum Discussion

gap's avatar
gap
Icon for Helper I rankHelper I
3 years ago

Filter Matrix Rows to be visible only when all columns have values

Hello

 

My goal is to hide rows on a matrix when not all columns have value. So i created the below filter. What i am counting is the the total columns on each row (columns will be the Year), where a specific measure has value. Then i am comparing this value with the total Years (so total columns), returning 1 if this is the same value. Then i will filter 1 on the Matrix. But this is not working 😞

 

When i am returning the _nonblankyears, then the value is the expected one as you can see on the first screenshot. When there is a missing value, then the Basket Quantities Filter returns for example 4 instead of 5 (5 are the selected years). When i filter the visual and enter the value 5, then the matrix is filtered correctly. Of course the _years also returns correct values (in this case 5 for all the columns).

 

But when i am using the 1,0 values to make the filter work more dynamicaly, then is not working anymore, and i am really struggling to figure out why.....

 

Basket Quantities Filter =

var _years = COUNTROWS(ALLSELECTED('Date'[Year]))
var _nonblankyears =
    COUNTROWS(
        FILTER(
            CROSSJOIN(
                VALUES(Items[Item Name]),
                ALLSELECTED('Date'[Year])
            ),
            NOT(ISBLANK([Basket Quantities Price]))
        )
    )
RETURN
IF(_nonblankyears = _years, 1)
 

 

 

 

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion

    Filter=COUNTROWS(FILTER(ALLSELECTED('Date'[Year]),ISBLANK([Basket Quantities Price]))

    then, select those rows get 0

    • gap's avatar
      gap
      Icon for Helper I rankHelper I

      Thanks. The problem is that it will filter the results only when the Filter measure is added to the values. Then i remove it (but of course keep it on the filters), then all the rows returned.