Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.