Forum Discussion
Matrix: Show blanks as 0
Hi,
I have the following measure:
I have a matrix with dates in columns and three other dimensions (product, market, kpi name) as rows.
For some of the matrix cells, I'll get blanks because there is no data. I want to replace all blanks with zeros so that any conditional formatting on my measure will also apply to those cells.
I've tried suggestions from this forum and other places already. Wrapping it in COALESCE, adding +0 at the end, IF-statement (if measure results in BLANK() then 0) etc.
All of these result in all of my matrix cells suddenly being 0.
Any suggestions on how to solve this?
Hi,
thank you all for the suggestions.
Unfortunately, none of them worked, but I did manage to figure out the solution.
In essence, one has to create a virtual table of all possible dimension combinations in the given filter context first, and then add 0 to these. This is necessary because some of the combinations simply don't exist in the data, and you can't add 0 to a datapoint that doesn't exist in the first place. The matrix visual plays a trick on you if you will, as it will show a cell that corresponds to a certain combination, but this datapoint doesn't really exist.
Here's the DAX code I used that works:KPI_GeneralMeasure_AC_FC_BU =var _ValueCombinationsToCheck =CALCULATETABLE(ADDCOLUMNS(VALUES( Dim_Date[Year Month] ),"Value",[KPI_Forecast] --sufficient to only check this one because it contains both actuals (for historical periods) and forecast for future periods. Budget should be without background color anyways),ALLSELECTED(Dim_Date))--Generates a virtual table with all possible combinations for the current date filter context. The idea is to check whether there is at least one value for some period in the current filter context, in order to then add +0 for those periods where there is no value, so that these show up as 0 in the matrix and will be affected by conditional formatting.var _ShowValue = NOT ISEMPTY( FILTER( _ValueCombinationsToCheck, [Value] <> 0 ) )--True if at least one value <>0 is found in the current filter context. If no value is found, we want to ignore/not show the entire row anyway (and not add +0)RETURNSWITCH(SELECTEDVALUE( _selection_Series[Selection], "FAKTISK" ),"FAKTISK",IF(_ShowValue,[KPI_Actual]+0,[KPI_Actual]),"PROGNOSE",IF(_ShowValue,[KPI_Forecast]+0,[KPI_Forecast]),"BUDSJETT",[KPI_Budget],BLANK())
6 Replies
BmejiaSuper User
I added a 0 instead of blank() at end and it seems to added 0, also added +0 at the end of the parenthesis ")+0" also worked. There might be something else going in your matrix if this does not work. I would probably make a copy and start removing each column at a time to see if there is maybe a field you adding that is not allowing it to add the zero.
- Royel
Hi GJLY by updating your measure blank() to 0 will give you the ecpected results.
KPI_GeneralMeasure_AC_FC_BU = SWITCH( SELECTEDVALUE(_selection_Series[Selection], "FAKTISK"), "FAKTISK", [KPI_Actual], "PROGNOSE", [KPI_Forecast], "BUDSJETT", [KPI_Budget], 0 )Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!
v-echaithraCommunity Support
Hi @GJLY ,
Thank you Royel , Bmejia for your inputs.We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.- v-echaithra
Hi GJLY ,
I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.
Thank you.
Hi,
thank you all for the suggestions.
Unfortunately, none of them worked, but I did manage to figure out the solution.
In essence, one has to create a virtual table of all possible dimension combinations in the given filter context first, and then add 0 to these. This is necessary because some of the combinations simply don't exist in the data, and you can't add 0 to a datapoint that doesn't exist in the first place. The matrix visual plays a trick on you if you will, as it will show a cell that corresponds to a certain combination, but this datapoint doesn't really exist.
Here's the DAX code I used that works:KPI_GeneralMeasure_AC_FC_BU =var _ValueCombinationsToCheck =CALCULATETABLE(ADDCOLUMNS(VALUES( Dim_Date[Year Month] ),"Value",[KPI_Forecast] --sufficient to only check this one because it contains both actuals (for historical periods) and forecast for future periods. Budget should be without background color anyways),ALLSELECTED(Dim_Date))--Generates a virtual table with all possible combinations for the current date filter context. The idea is to check whether there is at least one value for some period in the current filter context, in order to then add +0 for those periods where there is no value, so that these show up as 0 in the matrix and will be affected by conditional formatting.var _ShowValue = NOT ISEMPTY( FILTER( _ValueCombinationsToCheck, [Value] <> 0 ) )--True if at least one value <>0 is found in the current filter context. If no value is found, we want to ignore/not show the entire row anyway (and not add +0)RETURNSWITCH(SELECTEDVALUE( _selection_Series[Selection], "FAKTISK" ),"FAKTISK",IF(_ShowValue,[KPI_Actual]+0,[KPI_Actual]),"PROGNOSE",IF(_ShowValue,[KPI_Forecast]+0,[KPI_Forecast]),"BUDSJETT",[KPI_Budget],BLANK())- v-echaithra
Hi GJLY ,
Thank you for sharing your update and confirming that you dont have any issue. Please let us know if you need any further assistance.
Thanks again for your contribution!
