Keep all no blank rows in matrix in PowerBI with DAX

Question

In PowerBI, I have a table with 4 columns:

• ItemId
• OptionId
• Price
• IsItemSold

For each ItemId in the table, we have the same count of OptionIds with a flag IsItemSold which is "Yes" if the Option is sold and "No" if the option is not sold.

I created a matrix with

• ItemId on rows
• OptionId on columns
• Price on values
• IsItemSold on the matrix visual filter with value equal to "Yes"

The result of my table is this

The goal is to display only the ItemId where all columns of the matrix are non empty (no blanks in rows).

How can we do this with DAX and/or using PowerBI visual options ?

Marco

Answer 1

A possible solution could be to add each field into the filters on that visual specifically. You could then filter these fields for only those values that are not blank. You can get to the filters panel in the ribbon by clicking View -> Filters.

Answer 2

You can do this by creating a calculated column to determine, if the item id has values for all option IDs:

Filter Flag = 
    VAR ItemID='Table'[ItemId]
    VAR Val = CALCULATE(DISTINCTCOUNT('Table'[OptionID]),
                        FILTER('Table','Table'[ItemId]=ItemID),
                        FILTER('Table','Table'[IsItemSold]="Yes"))
    VAR Val_Total = CALCULATE(DISTINCTCOUNT('Table'[OptionID]),
                              FILTER('Table','Table'[IsItemSold]="Yes"))
RETURN IF(Val=Val_Total,"Yes","No")

Using VAR val, we count the number of distinct option IDs for the item which we sold. Then we compare this against the total count of Option IDs (Val_Total) and create Yes/No flags.

Once this column is created, you can use this as a filter and filter for YES. Hope this helps.