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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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