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

## Distinct count measure

Hi everyone,

I would like to do this measure :

calculate the number of interventions using the distinctcount function for which there is neither the "A61" nor the "A62" intervention, but also does not take into account other interventions including the co-response codes A61 AND A62 Here is the table. Table name: Table.  So for this example, the result will be : 3

Another information: there are a lot of data like this.

Thank you, My best regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I

## Re: Distinct count measure

Add a column that tracks if your Column1 has one A61/2 row
Has6162=
VAR thisOpCode=Table[Column1]
RETURN
IF(SUMX(FILTER(Table;Table[Column1]=thisOpCode);IF(Table[Column2] IN {"A61";"A62"};1;0))>=1;1;0)

this column will (should 🙂 ) contain 1 if that opcode has at least one A61, A62

Now your measure will count only the ones where that value is 0

Measure=DISTINCTCOUNT(FILTER(Table;Table[Has6162] =0);Table[Column1])

Check syntax and test, but should work

Did I answer your question? Mark my post as a solution!

PLEASE follow these instructions to ask your questions!

Proud to be a Super User!

1 REPLY 1
Super User I

## Re: Distinct count measure

Add a column that tracks if your Column1 has one A61/2 row
Has6162=
VAR thisOpCode=Table[Column1]
RETURN
IF(SUMX(FILTER(Table;Table[Column1]=thisOpCode);IF(Table[Column2] IN {"A61";"A62"};1;0))>=1;1;0)

this column will (should 🙂 ) contain 1 if that opcode has at least one A61, A62

Now your measure will count only the ones where that value is 0

Measure=DISTINCTCOUNT(FILTER(Table;Table[Has6162] =0);Table[Column1])

Check syntax and test, but should work

Did I answer your question? Mark my post as a solution!

PLEASE follow these instructions to ask your questions!

Proud to be a Super User!

## Helpful resources

Announcements

#### Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

#### Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors