Need Help with DAX formula to count Distinct for order numbers

[GomzeeR]

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

[wdx223_Daniel]

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

[GomzeeR]

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

[Samarth_18]

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

[GomzeeR]

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.

[GomzeeR]

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

[Samarth_18]

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