Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Replace "blank" with 0

Hi Community   I know this have been posted so many times but have not found a solution that really works in my example. I hope you are able to help me.   I have created a Table visualization tha...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi Anonymous 

     

    Download sample PBIX

     

    I've set up some dummy data and adding 0 to the measure gives me 0 when the result is blank.

     

     

    Regards

     

    Phil

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

Using your own PBIX file I don't have a problem it works as expected.  If I force DIVIDE to return BLANK(), by adding 0 to that result I get 0 for CTR.

Click Through Rate = 

DIVIDE(
	DISTINCTCOUNT('Email - Clicks'[ACS ID]),
	//COUNTA('Email - Delivery'[ID])
    BLANK()
) + 0

 

 

If you are concerned about the red column in your tablew, that is because CTR is now returning 0 instead of BLANK and your conditional formatting is set to show red with a value of 0.

 

When you say my suggestions are not working, what exactly isn't working?  What result are you getting that you are not expecting?

 

Regards

 

Phil

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

The below is using an example where the table is filtered to only show ID = DM129369

 

When I am not adding zero for CTR this is what I see - and are correct:

 

When adding the zero to the measure I get this result:

 

As you might see I suddenly get a lot a "Sales Companies" which I should not get as DM129369 is only for Sales Company = GIN