Reply
nhmpp
Helper I
Helper I

COUNT in a measure

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!

1 ACCEPTED SOLUTION
PabloDeheza
Solution Sage
Solution Sage

Hi there!

Try this:

CALCULATE(

DISTINCTCOUNT( OrderNumber ),

Type = "A"

)

Let me know if that helps!

View solution in original post

9 REPLIES 9
nhmpp
Helper I
Helper I

Hi,

 

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

nhmpp_0-1661196143048.png

 

Can you share how you wrote the DAX code?

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

Is TranYN a measure?

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

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. 

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

I figured it out, thank you!

PabloDeheza
Solution Sage
Solution Sage

Hi there!

Try this:

CALCULATE(

DISTINCTCOUNT( OrderNumber ),

Type = "A"

)

Let me know if that helps!

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)