Using DISTINCT COUNT on a Measure

[StevenUser123]

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!

[tamerj1 · Accepted solution]

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

[StevenUser123]

Thank you so much!

[FreemanZ]

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