cancel
Showing results for
Did you mean:
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:

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

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

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

Thanks

5 REPLIES 5
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 () )
)```
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 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?

EDIT:

Look at the picture....

DISTINCTCOUNT will return 3

without - 1 or the FILTER function filtering out the blanks

Highlighted
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

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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 46 members 887 guests
Recent signins: