cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
karkar Member
Member

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
Super User
Super User

Re: DAX

@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 () )
)
karkar Member
Member

Re: DAX

Thanks Sean, I will use the filter next time.

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

 

Thanks

Super User
Super User

Re: DAX

@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

karkar Member
Member

Re: DAX

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

 

v-huizhn-msft Super Contributor
Super Contributor

Re: DAX

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