cancel
Showing results for
Did you mean: Frequent Visitor

## Using DISTINCT COUNT on a Measure

Hello,

I have a measure that calculates quantity based on a calculation of attribute and value (As in, Attribute = QTY and Value = 1), summed as Current Quantity per line item. I am trying to figure out how to get a distinct count from this measure, so I can see how many are 0.

The data is arranged as follows(I've illustrated my request in the below "Distinct Count" column):

 Product name Account Name Current Quantity Distinct Count Product 1 Company 1 0 1 Product 2 Company 1 2 0 Product 3 Company 2 2 0 Product 4 Company 3 5 0 Product 1 Company 4 6 0 TOTAL 15 1

I can't use typical count functions since I am unable to reference the calculated measure in my table (Still learning DAX so I am unsure what step I'm missing). If a calculated column makes more sense, let me know. I'd like this to be filterable based on dimensions (brand, segment, etc.) and time (month, year).

Appreciate any help!

1 ACCEPTED SOLUTION  Super User

Hi @StevenUser123
Assuming all columns are coming from one table then you can use

``````Distinct Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'['Table'],
'Table'[Account Name],
"@QTY", [Current Quantity]
),
[@QTY] = 0
)
)``````

In case Product Name and Account ID columns are coming from two different dimension tables then you may use

``````Distinct Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'Sales',
'Product'['Table'],
'Customer'[Account Name],
"@QTY", [Current Quantity]
),
[@QTY] = 0
)
)``````

3 REPLIES 3  Super User

Hi @StevenUser123
Assuming all columns are coming from one table then you can use

``````Distinct Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'['Table'],
'Table'[Account Name],
"@QTY", [Current Quantity]
),
[@QTY] = 0
)
)``````

In case Product Name and Account ID columns are coming from two different dimension tables then you may use

``````Distinct Count =
COUNTROWS (
FILTER (
SUMMARIZE (
'Sales',
'Product'['Table'],
'Customer'[Account Name],
"@QTY", [Current Quantity]
),
[@QTY] = 0
)
)`````` Frequent Visitor

Thank you so much!  Community Champion

the table you showed seems like a table visual. You will get a solution much quicker if you proivde

1) raw data table

2) the code for the [Current Quantity] measure  