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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
katto16
Helper I
Helper I

Count if multiple conditions are fulfilled

Hey guys, so I have this kind of sample data, where I want to count the "retest pass" and "rework pass" in a day.

katto16_2-1669300398573.png

 

Basically, if any of the Part was changed and pass, it's a rework pass. If the whole line is duplicated, it's a retest pass. The reason why I mention the date because some parts that failed in previous days might appear again in the following days (see CC3)

If the Unit ID appears only once and pass, then it's not counted into any category. 

 

My first idea is to create variables for different conditions and when all the conditions are met, it will be counted as 1. But I'm lost at the part where I have to determine the change of Part while the Unit ID and the Date stay the same. I was thinking about the EARLIER function as well but it wasn't successful when I tried it out.

 

I already have a calculated column created for the other situation which is the first pass if it helps in any way. 

 

First Pass = 
VAR currentID = 'Table'[Unit ID]
VAR currentResult = 'Table'[Overall Result]
VAR CountofRows = COUNTROWS(FILTER(Table, Table[Unit ID] = currentID))
VAR ReturnCheck = IF(CountofRows = 1 && currentResult = "pass",1,0)
return ReturnCheck

 

Thanks in advance!

1 ACCEPTED SOLUTION
ddpl
Solution Sage
Solution Sage

@katto16 

 

Hey here I assuming the second last row Result = Pass...

 

Create a calculated column as below...

 

Requirement = 

IF(
    COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A])), 'Table'[Unit ID]) = 1 &&
    COUNTX(FILTER('Table', 'Table'[Part B] = EARLIER('Table'[Part B])), 'Table'[Unit ID]) = 1 &&
    COUNTX(FILTER('Table', 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) = 1 &&
    'Table'[Result] = "Pass",
"First Pass",
    IF(
        COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A]) && 'Table'[Part B] = EARLIER('Table'[Part B]) && 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) = 1 &&
        'Table'[Result] = "Pass",
        "Rework Pass",
        IF(
            COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A]) && 'Table'[Part B] = EARLIER('Table'[Part B]) && 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) > 1 &&
            'Table'[Result] = "Pass",
            "Retest Pass"
        )
    )
)

 

bin03.png

View solution in original post

2 REPLIES 2
ddpl
Solution Sage
Solution Sage

@katto16 

 

Hey here I assuming the second last row Result = Pass...

 

Create a calculated column as below...

 

Requirement = 

IF(
    COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A])), 'Table'[Unit ID]) = 1 &&
    COUNTX(FILTER('Table', 'Table'[Part B] = EARLIER('Table'[Part B])), 'Table'[Unit ID]) = 1 &&
    COUNTX(FILTER('Table', 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) = 1 &&
    'Table'[Result] = "Pass",
"First Pass",
    IF(
        COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A]) && 'Table'[Part B] = EARLIER('Table'[Part B]) && 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) = 1 &&
        'Table'[Result] = "Pass",
        "Rework Pass",
        IF(
            COUNTX(FILTER('Table', 'Table'[Part A] = EARLIER('Table'[Part A]) && 'Table'[Part B] = EARLIER('Table'[Part B]) && 'Table'[Part C] = EARLIER('Table'[Part C])), 'Table'[Unit ID]) > 1 &&
            'Table'[Result] = "Pass",
            "Retest Pass"
        )
    )
)

 

bin03.png

katto16
Helper I
Helper I

Let me just tag the top users for more reach @amitchandak @mangaus1111 @Greg_Deckler @Jihwan_Kim @ddpl 
Appreciate for any help possible. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors