Help Replacing Blank Values with 0 in DAX!

[baby_cabbage]

I am calculating the retention rate of our customers.

In the Power BI matrix visual, the columns represent the Day Count, and the rows show the Registration date. To illustrate, I am analyzing the cohort that registered on September 21st. On day 0, 38 people from that cohort visited our shop. On day 1, 10 people visited, and on day 2, 2 people visited. However, from day 3 to day 15, no one from that cohort visited, so the cells display blank values.

In DAX, I want to replace these blank values with 0. I have tried using COALESCE(), ISEMPTY, NOT(ISBLANK), and adding +0, but none of these approaches are working. How can I modify the following measure to return 0 instead of blank?

Number of Customer Visits =
CALCULATE( COUNT(TableA[CustomerID]) )

Thank you very much!😭

[Anonymous]

Hi @baby_cabbage ,

You can try formula like below:

Number of Customer Visits = 
IF (
    ISBLANK ( MAX(TableA[Customer Visits])),
    0,
    CALCULATE ( SUM( ( TableA[Customer Visits] ) )
))

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[divyed]

Hello @baby_cabbage ,

You can use IF condition to return desired value , something like :

NO_OF_CUST =

 VAR _CUst = CALCULATE(SUM(T_Registration[Cust_ID]))  // Replace with your measure, this is on dummy data

 RETURN

 IF (ISBLANK(_CUst)=TRUE(),0,_CUst)

You can replace your actual measure in if condition and should work

If this helps, please mark this as a solution. You can say special thank you by giving Kudos 🙂

Cheers.

[baby_cabbage]

Thank you for dedication. Unfortunately it couldn't work on my report 

[divyed · Accepted solution]

Hello @baby_cabbage ,

Strange, have you checked if correct relationship is setup ?

Ok, lets try couple of more things :

1. Use COUNTROWS with Values : This approach will return 0 where no customers exist 

Number of Customers visited =
IF(
COUNTROWS(VALUES(TableA[CustomerID])) = 0,
0,
COUNT(TableA[CustomerID])
)

2. 

Number of Customers  visited=
SUMX(
VALUES(Day_Count), // replace with actual field name
IF(COUNT(TableA[CustomerID]) = BLANK(), 0, COUNT(TableA[CustomerID]))
)

Please let us know if this works.

Cheers

[SachinNandanwar]

Just a wild guess.try something like this

Customer Vists = CALCULATE(SWITCH(TRUE(),NOT ISBLANK(COUNT(TableA[CustomerID])),COUNT(TableA[CustomerID]),0))

[baby_cabbage]

:'( thank you for your help but it doesn't work

[lbendlin]

Number of Customer Visits =
COALESCE(CALCULATE( COUNT(TableA[CustomerID]) ),0)

[baby_cabbage]

Any other alternative to COALESCE(), ISEMPTY, NOT(ISBLANK), and adding +0? These didnt work