COUNT in a measure

[nhmpp]

Hi,

Here is a sample table like the one I am working with

Order Number   -   Type

1                               A

1                               A

2                               B

3                               A

Some order numbers repeat, but I would like to only count each order number once, AND count only instances with Type "A". The result I am looking for using the example table above is

Order Number    -     Count

1                                 1

2                                 0

3                                 1

I cannot add a column, so I have to use measures.

Thank you!

[nhmpp]

Hi,

I am getting the error message below, as I cannot view it in any visual. 

[PabloDeheza]

Can you share how you wrote the DAX code?

[nhmpp]

Sure, it is:

TransCount = CALCULATE(DISTINCTCOUNT('Table'[order_number_id]), 'Table'[TranYN] = "Yes")

"Yes" and "No" are the two groups, rather than A and B

[PabloDeheza]

Is TranYN a measure?

[nhmpp]

Hi, Yes it is. Will this not work becaue of that?

[nhmpp]

I made that measure because there were many different values that count as a "Yes" (as well as many that count as a "no"), and I am not sure how to incorporate them all to be counted. 

[PabloDeheza]

You should not use a measure there, you should use the column in your table. If you have a column where you are taking the "Yes" value, use that.

Not sure exactly what you mean with "many different values that count as a "Yes" "

[nhmpp]

I figured it out, thank you!

[PabloDeheza · Accepted solution]

Hi there!

Try this:

CALCULATE(

DISTINCTCOUNT( OrderNumber ),

Type = "A"

)

Let me know if that helps!