topic Re: COUNT in a measure in DAX Commands and Tips

COUNT in a measure

nhmpp — Mon, 22 Aug 2022 18:44:35 GMT

Hi,

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

Order Number - Type

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!

Re: COUNT in a measure

PabloDeheza — Mon, 22 Aug 2022 19:10:07 GMT

Hi there!

Try this:

CALCULATE(

DISTINCTCOUNT( OrderNumber ),

Type = "A"

)

Let me know if that helps!

Re: COUNT in a measure

nhmpp — Mon, 22 Aug 2022 19:22:39 GMT

Hi,

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

Re: COUNT in a measure

PabloDeheza — Mon, 22 Aug 2022 19:26:25 GMT

Can you share how you wrote the DAX code?

Re: COUNT in a measure

nhmpp — Mon, 22 Aug 2022 19:29:09 GMT

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

Re: COUNT in a measure

PabloDeheza — Mon, 22 Aug 2022 19:37:53 GMT

Is TranYN a measure?

Re: COUNT in a measure

nhmpp — Mon, 22 Aug 2022 19:43:23 GMT

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

Re: COUNT in a measure

nhmpp — Mon, 22 Aug 2022 19:44:31 GMT

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.

Re: COUNT in a measure

PabloDeheza — Mon, 22 Aug 2022 19:52:02 GMT

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" "

Re: COUNT in a measure

nhmpp — Mon, 22 Aug 2022 20:11:04 GMT

I figured it out, thank you!