DAX

Question

Hello,&nbsp;I am trying to flag patients who had been given a medicine within 48 hours of admission.&nbsp;Any suggestions are highly appreciated.&nbsp;Count = DISTINCTCOUNT(query[24hourflag])-1Example:ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Admittime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MEDTIME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 24hourflag101&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01JAN2017&nbsp;:1AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01JAN2017&nbsp;:11PM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 101&nbsp;&nbsp;&nbsp;101&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01JAN2017&nbsp;:1AM &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 02JAN2017&nbsp;:6AM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; blank102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 08JAN2017:5PM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11JAN2017:5PM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; blank&nbsp;&nbsp;&nbsp;&nbsp;Thanks

sean · Answer

karkarDISTINCTCOUNT counts ALL blanks as 1 distinct valueTo get rid of the -1 use this measure instead&nbsp;Measure =
CALCULATE (
    DISTINCTCOUNT ( query[24hourflag] ),
    FILTER ( query, query[24hourflag] &lt;&gt; BLANK () )
)

karkar · Answer

Thanks Sean, I will use the filter next time.But&nbsp;can you&nbsp;explain what the -1 is doing in our previous formulae?&nbsp;Thanks

sean · Answer

karkarOkay =&gt;DISTINCTCOUNT counts ALL blanks as 1 distinct value&nbsp;This means ALL Rows in the [24Hourflag] Column that are BLANK will count as 1 =&gt; meaning this 1 will be added to the Total&nbsp;Say you have 5 distinct non-blank values&nbsp; =&gt; your formula without the -1 will return 6&nbsp;because 5 distinct non blank + 1 for all blanks&nbsp;so the - 1 deducts the blanks&nbsp;Does this make sense? :smileyhappy:&nbsp;EDIT:Look at the picture....DISTINCTCOUNT will return 3without - 1 or the FILTER function filtering out the blanks

karkar · Answer

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).&nbsp;When we say -1 , it removes the group which has a count of 1?&nbsp;Is that right??&nbsp;COUNT_ID&nbsp;&nbsp;&nbsp;&nbsp; COUNT_BKLANK5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;karkar,As the Sean&nbsp;said, DISTINCTCOUNT counts ALL blanks as 1 distinct value, all BLANK will count as 1. So&nbsp;&nbsp;-1 , it removes the BLANK group.Best Regards,Angelia

Forum Discussion

DAX

5 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX