Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I have spent way to much time trying to figure this one out and it seems like it should be easy, but I'm no expert. I am trying to apply a countx measure with a filter, but it is giving me the wrong count. I have attach a mock up pbix file. If you can provide an insight, it would be greatly appreciated.
The measures in question are the Q1 Above Zero and the Q1 Equals Zero.
https://www.dropbox.com/s/gbfb8nmwyp2bkrp/mockupforcountxissue.pbix?dl=0
Solved! Go to Solution.
I don't think the measure is really the problem, it is the underlying data. Thanks for the PBIX file - very helpful.
8 is the right answer, because COUNTX is looking at the rows in the actual table, and there are 8 that are zero.
The below will tell you the unique count of customers that are zero, but it still isn't showing you what is in your summary table. It just eliminates the duplicate customer numbers.
Q1 Equals Zero =
CALCULATE(
COUNTROWS(
DISTINCT(Sheet1[Customer])
),
FILTER(
Sheet1,
[Comp Q1 Form Score Diff] = 0
)
)
I think what you want is this, which returns 2, the number of 0's in the Q1 column:
Customer Totals are Zero =
COUNTROWS(
FILTER(
SUMMARIZECOLUMNS(
Sheet1[Customer],
"Total", SUM( Sheet1[AllData.Q1 Score] )
),
[Total] = 0
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI don't think the measure is really the problem, it is the underlying data. Thanks for the PBIX file - very helpful.
8 is the right answer, because COUNTX is looking at the rows in the actual table, and there are 8 that are zero.
The below will tell you the unique count of customers that are zero, but it still isn't showing you what is in your summary table. It just eliminates the duplicate customer numbers.
Q1 Equals Zero =
CALCULATE(
COUNTROWS(
DISTINCT(Sheet1[Customer])
),
FILTER(
Sheet1,
[Comp Q1 Form Score Diff] = 0
)
)
I think what you want is this, which returns 2, the number of 0's in the Q1 column:
Customer Totals are Zero =
COUNTROWS(
FILTER(
SUMMARIZECOLUMNS(
Sheet1[Customer],
"Total", SUM( Sheet1[AllData.Q1 Score] )
),
[Total] = 0
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |