topic Re: Need Help with DAX formula to count Distinct for order numbers in DAX Commands and Tips

Need Help with DAX formula to count Distinct for order numbers

GomzeeR — Wed, 21 Jul 2021 09:28:29 GMT

Hi all,

I have a data set, where an order number can have either one or multiple lines associated with it.

Ex:
OrderNO Type
12345 A

12345 B

12346 A

12347 B

12348 A

12348 B

I want to count all the order numbers that have either Type A or Type B only. I don't want to count Order numbers that have both Type A & Type B

Probably this is quite simple, but I am unable to achieve it.

Thanks in advice.

Kind regards,

Gowtham

Re: Need Help with DAX formula to count Distinct for order numbers

Samarth_18 — Wed, 21 Jul 2021 07:21:27 GMT

HI GomzeeR ,

You can use below code:-

Unique_count = COUNTROWS ( SUMMARIZE ( 'Table (2)', 'Table (2)'[OrderNO ], 'Table (2)'[Type] ) )

Below would be output

Hope you wanted the same output if not please share the expected output.

Thanks,

Samarth

Re: Need Help with DAX formula to count Distinct for order numbers

GomzeeR — Wed, 21 Jul 2021 07:28:41 GMT

Hi Samarth,

Thanks for your reply.

I want a count for Type A to be 1 and Type B to be 1(From the example data).
Count of Type A(unique) = 1
Count of Type B(Unique) = 1

I want to count only rows that has either A or B not both.

Kind regards,

Gowtham

Re: Need Help with DAX formula to count Distinct for order numbers

wdx223_Daniel — Wed, 21 Jul 2021 07:32:19 GMT

=countrows(filter(values(table[order no]),calculate(distinctcount(table[type]))=1))

Re: Need Help with DAX formula to count Distinct for order numbers

GomzeeR — Wed, 21 Jul 2021 07:51:35 GMT

Hi Daniel,

This formula is not working for me, I need to measure count for rows that has Type A only(which is one in the sample data). Same for Type B.

Kind regards,

Gowtham

Re: Need Help with DAX formula to count Distinct for order numbers

Samarth_18 — Wed, 21 Jul 2021 08:14:36 GMT

Hi GomzeeR

You can try below code it will help

Unique_count = COUNTROWS ( FILTER ( VALUES ( 'Table (2)'[Type] ), CALCULATE ( DISTINCTCOUNT ( 'Table (2)'[OrderNO ] ) > 1 ) ) )

Output:-

Thanks

Re: Need Help with DAX formula to count Distinct for order numbers

GomzeeR — Wed, 21 Jul 2021 09:23:33 GMT

Hi Samarth,

Thanks for taking time to help me.

If I add another Order No that has only Type A, my expected output should be.

TypeA - 2

TypeB - 1

Your suggested formula still shows, TypeA - 1 and TypeB - 2.

I want to display order numbers that have Only Type A or only Type B. I don't want to count Order numbers that have Both.

Hope you understood my expected output.