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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi,

 

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

Anonymous
Not applicable

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)
VasTg
Memorable Member
Memorable Member

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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