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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Susan513
Helper I
Helper I

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
Helper I
Helper I

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors