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
Sean Super Contributor
Super Contributor

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 () )
)
Highlighted
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

Sean Super Contributor
Super Contributor

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

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 56 members 1,013 guests
Please welcome our newest community members: