Forum Discussion

StevenUser123's avatar
StevenUser123
Frequent Visitor
3 years ago
Solved

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!

  • tamerj1's avatar
    tamerj1
    3 years ago

    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

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

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