Forum Discussion
Exclude Columns with ALL zero values
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.
8 Replies
Hi,
In Power query unpivot the dates column and then use matrix in the desktop.
see how it works out.
Unpivotting is multiplying the values but still loosing the zeros when I filter NULLS.
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.
marjoriefialek , you can try like this
measure =
var _1 = calculate([measure], allexcept(Table[Date])) +0
return
calculate([measure],_1 <>0 )
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.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.
any luck with this at the end? thanks 🙂
