Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Susan513
Frequent Visitor

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

Susan513_0-1722452713217.png

 

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
Susan513
Frequent Visitor

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.

Susan513_0-1722605778859.png

 

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!

Ahmedx
Super User
Super User

pls try this

Screenshot_2.png

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors