Matrix Visual

[aditya_141228]

This is a matrix visual with some columns however what I need in this is that I want to display records of only those which have values in any one of the columns and I don't want to display records that has values 0 or null in every column.

What's the best way to achieve this?

Any help or suggestions will be appreciated.

Thank You

[Anonymous]

Hi @aditya_141228

Thanks for the reply from @DataNinja777,please allow me to provide another insight: 

You can use filters to achieve this.

For example, my original data is like this.

Use filters to filter out results where the rate column is not blank and not 0.

 

For more details about filters, refer to the document: Add a filter to a report in Power BI - Power BI | Microsoft Learn.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[aditya_141228]

Hi @Anonymous 
Thank You for replying and giving your attention to the issue, the insight that you provided is correct however my requirement is a bit different here. Suppose I've added 10 fields in my matrix in the values section, my requirement would be that if all the values in these 10 fields are zero or blank then I don't want that entry to reflect in the Matrix or Table visual but if any of the 10 fields has any non zero value then that entry should be visible. 

Please do reply if you have further doubts over my confusion.

Any help or suggestions will be appreciated.

[Anonymous]

Hi @aditya_141228 ,

1. Create a measure that adds the values ​​of all columns and returns the distinct value.

MEASURE =
IF (
SUM ( 'Table'[%] ) + SUM ( 'Table'[num1] )
+ SUM ( 'Table'[num2] )
+ SUM ( 'Table'[rate] ) > 0,
1,
0
)

2. Filter out the values ​​with a value of 1 in the filter.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[aditya_141228]

Hi @Anonymous ,

I'm using some measures in the field too and it's taking only columns that existed in the table or the calculated columns that I've added. What will be the way to do the same for measures too?

[Anonymous · Accepted solution]

Hi @aditya_141228 ,

You can try this.

MEASURE = 
IF(NOT('Table'[Measure 2] = 0 || 'Table'[Measure 2] = BLANK()) ||
NOT('Table'[Measure 3] = 0 || 'Table'[Measure 3] = BLANK()) ||
NOT('Table'[Measure 4] = 0 || 'Table'[Measure 4] = BLANK()) ||
NOT('Table'[Measure 5] = 0 || 'Table'[Measure 5] = BLANK()),1,0)

Best Regards,

Clara Gong

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

[aditya_141228]

@Anonymous 
Thank You for the insight it is working fine, though I need to add this new measure in my table otherwise it is not getting placed in the filter for this visual pane but I've managed to add the measure. This is the solution so far. Do let me know if there is any other way around which works even if I do not add the measure in the matrix or table visual.

[DataNinja777]

Hi @aditya_141228 ,

I suppose there are multiple ways to produce your required output, and one of them is to wrap your measures in  formulas like below to supress zeros in visual.

However, if you have many measures, doing this one by one may take long time, in which case calculation group may be useful (even though I never used that myself.)  

Best regards,