Matrix visual not filtering the data for all months

[Anonymous]

Hi,

I have created a calculated table by combining the two tables using below formula.

Combined NRR = UNION(SELECTCOLUMNS('DME NRR',"System",'DME NRR'[System],"Date",'DME NRR'[date],"Region",'DME NRR'[Region],"Owner",'DME NRR'[Owner],"Reseller",'DME NRR'[Reseller Name],"Revenue",'DME NRR'[usd amount],"Revenue_Last year",'DME NRR'[usd_amount PY],"Product","DME"),SELECTCOLUMNS('DMS NRR',"System",'DMS NRR'[System],"Date",'DMS NRR'[Date],"Region",'DMS NRR'[Region],"Owner", 'DMS NRR'[Owner],"Reseller",'DMS NRR'[Reseller],"Revenue",'DMS NRR'[Revenue],"Revenue_Last Year",'DMS NRR'[Revenue_PY],"Product","DMS"))

I have created a matrix visual out of the table as below and filtering the revenue_last year column is not blank

 but its not filtering the data as expected. if revenue_last year is blank it should not show data in Revenue. for example on the first row of the above screenshot, its still showing the value in revenue even though revenue_last year is blank.

I have created a same visual from another calculated table and applied the same filter. it works as expected.

Not sure what is the issue here. Could you please advise if i am missing something.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

If you turn the matrix into a table, you will find that it can be successfully filtered. This is by design. The matrix applies the measure/value filter at the Row totals granularity, not at the cell granularity.

Take a simple example, for the below sample.

Put it in a matrix.

In the visual filter, if the setting value is greater than 5, then the Row B is filtered out, but Row A not, because for Row A, the value is 10(1+2+3+4).

If the setting value is greater than 10, both the two rows are filtered out.

Additionally, when a measure is applied to a visual filter, each row produces a result, not each cell.

Still take the above sample as an example, I create a measure.

Measure = MAX('Table'[Value])

When I put the measure in the visual filter, for each cell in Row A, the measure result is 4 (which is the maxmum in the row).

So when the measure filter is greater than 3, no row be filtered out.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

@amitchandak Could you please help on this?