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 a datatable similar to the following:
Category | ID | date | good | bad | undecided |
Cat1 | 11AA | some_date | 1 | 0 | 0 |
Cat1 | 11AA | some_date | 1 | 0 | 0 |
Cat1 | 22BB | some_date | 1 | 0 | 0 |
Cat1 | 22BB | some_date | 1 | 0 | 0 |
Cat1 | 22BB | some_date | 0 | 1 | 0 |
Cat2 | 33CC | some_date | 1 | 0 | 0 |
Cat2 | 33CC | some_date | 0 | 1 | 0 |
Cat2 | 33CC | some_date | 0 | 0 | 1 |
Cat2 | 44DD | some_date | 1 | 0 | 0 |
Cat2 | 44DD | some_date | null | null | null |
The rules are:
What I need are measures:
So the result visual matrix should look like this:
Category | ID | Measure 1 [good] | Measure 2 [bad] | Measure 3 [undecided] |
Cat1 | 1 | 1 | 0 | |
11AA | 1 | 0 | 0 | |
22BB | 0 | 1 | 0 | |
Cat2 | 1 | 0 | 1 | |
33CC | 0 | 0 | 1 | |
44DD | 1 | 0 | 0 |
I can't figure it out, I tried with SUMMARIZECOLUMNS and GROUPBY functions but in each approach the problem was I couldnt summarize the result table.
Thank you in advance!
Solved! Go to Solution.
Hello @Anonymous,
Please create following measures:
Good Rating =
VAR SelectedID = SELECTEDVALUE(Rating[ID])
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR GoodRatingCount = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[good]=1 || ISBLANK(Rating[good])))
VAR GoodRating = IF(TotalValues=GoodRatingCount,1,0)
VAR CategoryRatingTable = FILTER(SUMMARIZE(Rating,Rating[Category],Rating[ID],"GoodRating",CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[good]=1 || ISBLANK(Rating[good]))),"TotalValues",CALCULATE(COUNT(Rating[ID]),ALLEXCEPT(Rating,Rating[ID]))),[GoodRating]=[TotalValues])
RETURN IF(ISBLANK(SelectedID),COUNTX(CategoryRatingTable,Rating[ID]),GoodRating)
Bad Rating =
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR BadRating = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[bad]=1 || ISBLANK(Rating[bad])))
VAR Undecided = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[undecided]=1 || ISBLANK(Rating[undecided])))
RETURN IF(BadRating>=1 && Undecided=0,1,0)
Undecided Rating =
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR Undecided = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[undecided]=1 || ISBLANK(Rating[undecided])))
RETURN IF([Good Rating]=0 && [Bad Rating]=0 && Undecided>0,1,0)
The result is as follows:
Hope this helps.
Hello @Anonymous,
Please create following measures:
Good Rating =
VAR SelectedID = SELECTEDVALUE(Rating[ID])
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR GoodRatingCount = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[good]=1 || ISBLANK(Rating[good])))
VAR GoodRating = IF(TotalValues=GoodRatingCount,1,0)
VAR CategoryRatingTable = FILTER(SUMMARIZE(Rating,Rating[Category],Rating[ID],"GoodRating",CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[good]=1 || ISBLANK(Rating[good]))),"TotalValues",CALCULATE(COUNT(Rating[ID]),ALLEXCEPT(Rating,Rating[ID]))),[GoodRating]=[TotalValues])
RETURN IF(ISBLANK(SelectedID),COUNTX(CategoryRatingTable,Rating[ID]),GoodRating)
Bad Rating =
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR BadRating = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[bad]=1 || ISBLANK(Rating[bad])))
VAR Undecided = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[undecided]=1 || ISBLANK(Rating[undecided])))
RETURN IF(BadRating>=1 && Undecided=0,1,0)
Undecided Rating =
VAR TotalValues = CALCULATE(COUNT(Rating[ID]),FILTER(ALL(Rating),Rating[ID]=SELECTEDVALUE(Rating[ID])))
VAR Undecided = CALCULATE(COUNT(Rating[ID]),FILTER(Rating,Rating[undecided]=1 || ISBLANK(Rating[undecided])))
RETURN IF([Good Rating]=0 && [Bad Rating]=0 && Undecided>0,1,0)
The result is as follows:
Hope this helps.
Thank you, @rajulshah!
It took me some time to adjust your solution to my real data (I had to add lot's of additional filtering there) but the first measure works as I need it to work and thus I'm accepting your idea as the solution to my problem. I'll work with other two measures now but these probably won't be as problematic as the first one (less customization).
Covering 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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |