Exclude Columns with ALL zero values

[marjoriefialek]

I have a matrix visual with conditional coloring and icons.  In order to accomplish this I had to convert NULLS into zeros.  However, I would like to exclude the columns that have all ZEROS.  In Power Query, when I remove the date columns with nulls I also loose the converted values to zero.  

Examples are included.  

The first image I have all dates with conditional formatting.  The columns in red with NO values at all need to not be there.  

The second image is the same matrix but I excluded the date column with NULLS which eliminates the columns with ALL Zeros but I also loose the Zeros for the other rows/columns and I need them for conditional formatting using values.  I can't replace the NULLS with zeros because they were filtered by removing the NULLS from the date column. 

I was thinking I could use the Grand Total of the column to exclude the column but it doesn't work. 

Any help is appreciated.

[procy]

any luck with this at the end? thanks 🙂

[v-jingzhang]

Hi @marjoriefialek 

You can add a column to work as a flag: if the sum total value on a date is not zero, then flag is 1; if the sum total value on a date is zero, then flag is 0. Then apply this flag column to the matrix as a visual filter which sets value is 1.

Flag = IF(CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Date]))<>0,1,0)

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[marjoriefialek]

Hello @v-jingzhang  I still loose the other zeros in the columns.  

[v-jingzhang]

@marjoriefialek 

I suggest remaining all Zeros rather than NULLs in Power Query just as the first image in your original post, then adding the flag column as a filter with value 1. Here is a sample PBIX file for reference. Let me know if you have any questions.

[amitchandak]

@marjoriefialek , you can try like this

measure  =

var _1 = calculate([measure], allexcept(Table[Date])) +0

return

calculate([measure],_1 <>0 )

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Show the problematic table and please remove the unnecessary tables and visuals from the PBI file.

[aj1973]

Hi,

In Power query unpivot the dates column and then use matrix in the desktop. 

see how it works out.

[marjoriefialek]

Unpivotting is multiplying the values but still loosing the zeros when I filter NULLS.