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
karkar
Helper III
Helper III

DAX

Hello,

 

I am trying to flag patients who had been given a medicine within 48 hours of admission.

 

Any suggestions are highly appreciated.

 

Count = DISTINCTCOUNT(query[24hourflag])-1

Example:

ID               Admittime                        MEDTIME                     24hourflag

101            01JAN2017 :1AM            01JAN2017 :11PM        101   

101           01JAN2017 :1AM            02JAN2017 :6AM           blank

102          08JAN2017:5PM              11JAN2017:5PM            blank  

 

 

Thanks

5 REPLIES 5
Sean
Community Champion
Community Champion

@karkar

DISTINCTCOUNT counts ALL blanks as 1 distinct value

To get rid of the -1 use this measure instead

 

Measure =
CALCULATE (
    DISTINCTCOUNT ( query[24hourflag] ),
    FILTER ( query, query[24hourflag] <> BLANK () )
)

Thanks Sean, I will use the filter next time.

But can you explain what the -1 is doing in our previous formulae?

 

Thanks

Sean
Community Champion
Community Champion

@karkar

Okay =>DISTINCTCOUNT counts ALL blanks as 1 distinct value

 

This means ALL Rows in the [24Hourflag] Column that are BLANK will count as 1 => meaning this 1 will be added to the Total

 

Say you have 5 distinct non-blank values  => your formula without the -1 will return 6

 

because 5 distinct non blank + 1 for all blanks

 

so the - 1 deducts the blanks

 

Does this make sense? Smiley Happy

 

EDIT:

Look at the picture....

DISTINCTCOUNT will return 3

without - 1 or the FILTER function filtering out the blanks

DISTINCTCOUNT - Counts Blanks as 1.png

So it will group all the Non Blanks (counts all non blanks which is 5 in your example) and all the blanks(count will be 1 for all of them).

 

When we say -1 , it removes the group which has a count of 1?

 

Is that right??

 

COUNT_ID     COUNT_BKLANK

5                             1

 

Hi @karkar,

As the @Sean said, DISTINCTCOUNT counts ALL blanks as 1 distinct value, all BLANK will count as 1. So  -1 , it removes the BLANK group.

Best Regards,
Angelia

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.