cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TaylorLile
Frequent Visitor

Display 0 Instead of blank

Hello,

I know this question has been posted before, but I've tried their solutions and they don't work for me. I'm trying to caclulate the number of distinct presription codes per patient by using the following:

 

Perscriptions = (CALCULATE(DISTINCTCOUNT(Claims[RxCd]),ALLEXCEPT(Claims,Claims[patient_id])) - 1)
 
This is working perfectly, except the patient IDs that are in other tables and not in the claims table show up as blank and dissapear. Is there a way to dispay as 0, not blank?
 
I've tried doing an IF statement like below, but it doesn't change anything. I'm not sure why. 
 
IF(ISBLANK(
(CALCULATE(DISTINCTCOUNT(Claims[RxCd]),ALLEXCEPT(Claims,Claims[patient_id])) - 1)),0,CALCULATE(DISTINCTCOUNT(Claims[RxCd]),ALLEXCEPT(Claims,Claims[patient_id])) - 1)
 
Thanks for any help you can provide. 
 
1 ACCEPTED SOLUTION

Accepted Solutions
rickymazz Resolver I
Resolver I

Re: Display 0 Instead of blank

Let's try in this way
 
Perscriptions =
VAR pr = CALCULATE(DISTINCTCOUNT(Claims[RxCd]),ALLEXCEPT(Claims,Claims[patient_id])) - 1

RETURN IF(pr == BLANK() || pr <= 0, 0, pr)

View solution in original post

3 REPLIES 3
Super User I
Super User I

Re: Display 0 Instead of blank

@TaylorLile 

 

Maybe move the calculate into a variable and check the variable as blank.

 

Post some sample data to better assist you.

Connect on LinkedIn
rickymazz Resolver I
Resolver I

Re: Display 0 Instead of blank

Let's try in this way
 
Perscriptions =
VAR pr = CALCULATE(DISTINCTCOUNT(Claims[RxCd]),ALLEXCEPT(Claims,Claims[patient_id])) - 1

RETURN IF(pr == BLANK() || pr <= 0, 0, pr)

View solution in original post

Yachika
Frequent Visitor

Re: Display 0 Instead of blank

Hi,

 

Please try the formula IF(TableName[ColumnName]<=10,"100","0")

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors