Forum Discussion
Check for values in group
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:
- There is always just one value in columns "good", "bad" and "undecided" for each row.
- There may be rows with null values in all of those columns, assume then it's "good".
What I need are measures:
- Measure 1: If all values for certain ID are "good" return 1, else return 0.
- Measure 2: If there is any "bad" value check, if there is any "undecided" value and choose last one by date.
- Measure 3: If there is any "undecided value check, if there is any "bad" value and choose last one by date.
So if there is one bad and one undecided the result value for this ID should be the last one.
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!
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.
2 Replies
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).