Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lawada
Helper III
Helper III

unable to replace blank with zeros in matrix

 

im trying to replace blank values with zeros in the following matrix :

 

lawada_0-1636289347629.png

 

the measure im using:

total users = CALCULATE(DISTINCTCOUNT(canceled_then_returned_cohort[user_id]))+0
note that both columns im using in the matrix doesnt have blank values and my datasource is redshift
 
is there any other solution than adding '+0' at the end of the measure since it's not working

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

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 _ kalyj

If 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 

AlexisOlson
Super User
Super User

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?

Fowmy
Super User
Super User

@lawada 

Can you check your source data if you have data for September 2018 on 10th ? 
If there is not data, the cell in the matrix cannot be controlled

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors