Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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!
Solved! Go to 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
)
)
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!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
11 | |
11 |