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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors