cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
StevenUser123
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 nameAccount NameCurrent QuantityDistinct Count
Product 1Company 101
Product 2Company 120
Product 3Company 220
Product 4Company 350
Product 1Company 460
TOTAL 151

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
tamerj1
Super User
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
    )
)

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
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
    )
)

 

Thank you so much!

FreemanZ
Community Champion
Community Champion

hi @StevenUser123 

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors