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 Experts,
I need your help with a measure issue:
Objective: My measure should count distinct number of 'products' based in each card visual for:
High Users High Usage (Users >=50 && Usage >=50000)
Low Users Low Usage(Users <= 49 && Usage <= 49999)
High Users Low Usage (Users >=50 && Usage between 1 to 49999)
Low Users High Usage (Users between 1 to 49 && Usage >= 50000)
Sample Data:
'Product' Table:
Product Name | Year | Users | Usage
Product1 | 2022 | 20 | 45000
Product2 | 2022 | 10 | 10000
Product3 | 2022 | 10 | 1000
Product1 | 2023 | 30 | 40000
Product2 | 2023 | 20 | 10000
Product3 | 2023 | 10 | 1000
Required logic: Loop through each product and find the total:
1) Total Users = SUM([ProductName]+[Year=2022][Users] & [ProductName][Year=2023][Users])
2) Total Usage = SUM([ProductName]+[Year=2022][Usage] & [ProductName][Year=2023][Usage])
3) If [Total Users] >= 50 and [Total Usage] >= 50000 then count distinct number of rows
Example:
Based on my sample data, the following calculations need to happen accordingly:
Total Users : Product1(2022) + Product1(2023) = 20 + 30 = 50
Total Usage : Product1(2022) + Product1(2023) = 45000 + 40000 = 85000
Total Users : Product2(2022) + Product2(2023) = 10 + 20 = 30
Total Usage : Product2(2022) + Product2(2023) = 10000 + 10000 = 20000
Total Users : Product3(2022) + Product3(2023) = 10 + 10 = 20
Total Usage : Product3(2022) + Product3(2023) = 1000 + 1000 = 2000
Expected Result:
The result of my card visual should be following values:
Card Visual 1: High Users High Usage: 1
Card Visual 2: High Users Low Usage: 0
Card Visual 3: Low Users High Usage: 0
Card Visual 4: Low Users Low Usage: 2
This is an example of my current measure: for High Users High Usage. I have changed conditions for other calculations accordingly.
CALCULATE ( DISTINCTCOUNT('Product'[ProductName]), 'Product'[Users] >= 50 && 'Product'[Usage] >= 50000)) + 0
Problem: I have a year slicer, if I click on 2022 and 2023 individually, the counts are accurate but the combined years total count is wrong on my card visual.
Can you please let me know what the issue is in my DAX. Appreciate all the help in advance!! @lbendlin
Thanks,
Ashwini
Like this?
Thank you @lbendlin . I brokedown the query to suit each one card visual - High users High Usage, High Users Low Usage, Low users High Usage, Low Users Low Usage.
The issue I have a slicer for 'Year' and it used to show individual count for a particular year.
Now when I click on individual year selection, it breaks my measure (Shows an error - See details). How can I keep the individual year count when I select year 2022 or 2023 but it should show the combined count when year is NOT selected (Default or all selected).
@lbendlin Thanks so much again!!
don't see an error message
Sorry I meant the standard error. It no longer shows the count when I click on 2022 and 2023 individually.
please post the sample pbix.
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Hello @lbendlin ,
As mentioned in my initial post, it is a sample mocked up data to make sure my measures were working correctly. What happened after the update I made based on your response, it wiped out my calculations that were correctly working individually for each year. Also, as mentioned I have a 'Year' Slicer. To clarify, when I click on 2022, it should show me 2022 counts, similarly for 2023. When I unselect both years (default selection), it should show me combined count.
Thanks,
Ash
I cannot assist you if you are unable to provide sample data. I hope someone else can help you further.
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 |
---|---|
30 | |
26 | |
23 | |
20 | |
15 |
User | Count |
---|---|
56 | |
37 | |
28 | |
24 | |
21 |