Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • rajulshah's avatar
    rajulshah
    6 years ago

    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.

rajulshah's avatar
rajulshah
Resident Rockstar
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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).