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

Highlighted
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 887 guests
Please welcome our newest community members: