Filter a Matrix to show months with zero

[MartinJoughin]

I've got a matrix that shows clients and what they paid each month:

How can I filter it so it shows client who had a month with zero fees (the red squares)? 

[Anonymous · Accepted solution]

Hi @MartinJoughin ,

According to your statement, I think you want to show the clients who have blank data in matrix.

Here I use  Jihwan_Kim's sample to create a measure.

Measure = 
VAR _GENERATE =
    GENERATE (
        CALCULATETABLE ( VALUES ( client[client] ), ALLSELECTED(sales)),
        CALCULATETABLE (
            VALUES ( 'calendar'[Year-Month] ),
            ALLSELECTED(sales)
        )
    )
VAR _SALES =
    ADDCOLUMNS (
        _GENERATE,
        "Sales",
            VAR _client = [client]
            VAR _YearMonth = [Year-Month]
            RETURN
                CALCULATE (
                    SUM ( sales[sales] ),
                    FILTER (
                        ALLSELECTED(sales),
                        sales[client] = _client
                            && FORMAT ( sales[date], "YYYY-MMM" ) = _YearMonth
                    )
                ) + 0
    )
VAR _SUMMARIZE =
    SUMMARIZE (
        _SALES,
        [client],
        "Product", PRODUCTX ( FILTER ( _SALES, [client] = EARLIER ( [client] ) ), [Sales] )
    )
RETURN
    SUMX ( FILTER(_SUMMARIZE,[client] = MAX(client[client])), [Product] )

Add this measure into visual level of matrix visual and set it to show items when value = 0.

Before:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

I am not sure how your expected outcome looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.