Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello everyone. I need help with the following question.
In the dataset, I evaluate things like "good" or "bad". We need to count the times that "bad" responses repeats itself more than once for the place and the item being evaluated.
This is an example:
* Bad Responses are items evaluated as bad: Bad Responses
Bad Responses = CALCULATE( COUNTROWS (Table[Response]), Response == 'Bad')
* Wrong Measure is the number of times "Bad Response" appeared more than once. I thought it would work as (Bad Response - 1) as it is right now, but the grand total is wrong - I suspect that it is calculating "[Bad Responses] -1", subtracting = the totals, and not the sum of the row values as I need it to.
Sample data below:
ID | Place | Item Name | Response |
1 | Copacabana | Item 1 | Bad |
2 | Copacabana | Item 1 | Bad |
3 | Copacabana | Item 1 | Bad |
4 | Copacabana | Item 2 | Bad |
5 | Copacabana | Item 2 | Bad |
6 | Corporate Park | Item 1 | Bad |
7 | Corporate Park | Item 2 | Bad |
8 | Corporate Park | Item 2 | Bad |
The expected output is:
Place | Bad Ratings | Recurrence |
Copacabana | 5 | 3 |
Corporate Park | 3 | 1 |
TOTAL | 8 | 4 |
The 4 in Recurrence is the 2 times "Item 1" in Copacabana was rated "bad" beyond the first bad rating, plus one bad rating to "Item 2" beyond the first, and plus one bad rating from "Item 2" on Corporate Park beyond the first bad rating on Item 2.
I tried constructing a dimension table and doing a sum, but the date property of the data would not function correctly when using slicers.
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
Please create following measures:
Count_bad = CALCULATE(COUNT('Table'[Response]),ALL('Table'[Item Name]))
Count_bad-1 = IF(COUNT('Table'[Response]) >1,COUNT('Table'[Response])-1)
Count_all_bad-1 = SUMX(ALL('Table'[Item Name]),[Count_bad-1])
Count_sum_bad-1 = SUMX(ALL('Table'[Place]),[Count_all_bad-1])
Bad_response = IF(ISINSCOPE('Table'[Place]), [Count_all_bad-1],[Count_sum_bad-1])
You will get the result you want:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please create following measures:
Count_bad = CALCULATE(COUNT('Table'[Response]),ALL('Table'[Item Name]))
Count_bad-1 = IF(COUNT('Table'[Response]) >1,COUNT('Table'[Response])-1)
Count_all_bad-1 = SUMX(ALL('Table'[Item Name]),[Count_bad-1])
Count_sum_bad-1 = SUMX(ALL('Table'[Place]),[Count_all_bad-1])
Bad_response = IF(ISINSCOPE('Table'[Place]), [Count_all_bad-1],[Count_sum_bad-1])
You will get the result you want:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous Try:
Measure =
VAR __Table = 'Table'
VAR __Table1 = GROUPBY(FILTER(__Table,[Item],[Response] = "Bad"),"__Count",COUNTX(CURRENTGROUP(),[ID]))
VAR __Rows = COUNTROWS(__Table1)
VAR __Result = SUMX(__Table1,[__Count]) - __Rows
RETURN
__Result
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |