Forum Discussion
Return value if all rows in multiple categories match
Looking for solution to evaluate multiple rows based on multiple categories. In my example, I want to know per site, per category - if all the documents in the sites' category are resolved for the site, then indicate in the result column - "Complete" for all those rows.
If any of the documents are not resolved in the particular category, then all the documents in this category would be marked in the result column as Incomplete.
I want to know, Yes a category is complete or No it isn't and be able to show the categories in my report by site. I was thinking a measure, but I am not certain. I am not sure how to ask for ALL the rows to meet the condition - where the column Status for each category = "Resolved" per site. All resolved = Complete If they are not all Resolved, then Incomplete.
Thank you
pls try this
RESULT = VAR _t1 = CALCULATE(COUNTROWS(),ALLEXCEPT('Table','Table'[Site],'Table'[Category])) VAR _t2 = CALCULATE(COUNTROWS(),ALLEXCEPT('Table','Table'[Site],'Table'[Category]),'Table'[Status]="Resolved" ) RETURN if (_t1 = _t2 ,"Complete", "Incomplete" )
4 Replies
pls try this
Thank you for addressing - it looked to work - but I added one more row on Site A for bikes #88, and it is not flagging the Cars as Complete. I think it needs to also look at the category, as the comparison is for a site, and also the rows in each category. Teh Site A rows for Cars are resolved - so the result should be Complete. I am getting Incomplete.
pls try this
RESULT = VAR _t1 = CALCULATE(COUNTROWS(),ALLEXCEPT('Table','Table'[Site],'Table'[Category])) VAR _t2 = CALCULATE(COUNTROWS(),ALLEXCEPT('Table','Table'[Site],'Table'[Category]),'Table'[Status]="Resolved" ) RETURN if (_t1 = _t2 ,"Complete", "Incomplete" )Thank you - this is working, I appreciate you!
