Check for values in group

[Anonymous]

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!

[rajulshah · Accepted 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.

[Anonymous]

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