Forum Discussion
unable to replace blank with zeros in matrix
im trying to replace blank values with zeros in the following matrix :
the measure im using:
As Fowmy points out, if there are no rows in the data table corresponding to a particular cell, then it can't return anything since there's nothing to evaluate. It doesn't matter if you add "+0" to the measure since it never gets called in the first place.
The solution to this is to use dimension tables to populate the rows and columns in the visual. I.e., a date table for the rows and some sort of index table for the columns. This way, the measure you put in the matrix cells lives in the cross-join of the dimensions rather than being limited to the subset that intersects with your data table.
5 Replies
As Fowmy points out, if there are no rows in the data table corresponding to a particular cell, then it can't return anything since there's nothing to evaluate. It doesn't matter if you add "+0" to the measure since it never gets called in the first place.
The solution to this is to use dimension tables to populate the rows and columns in the visual. I.e., a date table for the rows and some sort of index table for the columns. This way, the measure you put in the matrix cells lives in the cross-join of the dimensions rather than being limited to the subset that intersects with your data table.
this solution is correct but its not working with me . after i created both calender table and and index column , joined with the data table and then used the two columns from calender and index table within the created measure it keeps loading in the visual but doesnt show the result at the end . note that my data table source is in direct query mode, would this affect on the speed of the data loading in the visual?
Hi lawada ,
According to your description, you can use IF function, like this:
Total users = IF(ISBLANK(CALCULATE(DISTINCTCOUNT(canceled_then_returned_cohort[user_id]))),”0”,CALCULATE(DISTINCTCOUNT(canceled_then_returned_cohort[user_id]))
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this solution doesnt work, as mentioned in the answers below theres no rows in the data table
