cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hicham Member
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. 

 

65+.PNG

Thank you, My best regards. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
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!




View solution in original post

1 REPLY 1
Super User I
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!




View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

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!

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?

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

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

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors