Column that counts rows from two other columns and compares the result

[nok]

Hello! I'm having trouble doing something that seems simple.
My table follows this structure:

ID	STATUS	LAST_STEP
111	Not approved	TRUE
111	Approved	TRUE
222	Approved	TRUE
222	Approved	TRUE
222	Not approved	FALSE
333	Approved	FALSE
333		FALSE
333	Approved	TRUE
333	Approved	TRUE
444	Approved	TRUE
444		TRUE

I want to create a calculated column that checks, for each ID, if the number of rows where STATUS="Approved" and the number of rows where LAST_STEP=TRUE are the same. If they are the same, then this new column would show "OK" (if not it shows "NOK").

The expected result would be something like this:

ID	STATUS	LAST_STEP	NEW_COLUMN
111	Not approved	TRUE	NOK
111	Approved	TRUE	NOK
222	Approved	TRUE	OK
222	Approved	TRUE	OK
222	Not approved	FALSE	OK
333	Approved	FALSE	OK
333		FALSE	OK
333	Approved	TRUE	OK
333	Approved	TRUE	OK
444	Approved	TRUE	NOK
444		TRUE	NOK

[DataInsights · Accepted solution]

@nok,

Try this calculated column:

New Column = 
VAR vID = 'Table'[ID]
VAR vStatusApproved =
    COUNTROWS ( FILTER ( 'Table', 'Table'[ID] = vID && 'Table'[STATUS] = "Approved" ) )
VAR vLastStepTrue =
    COUNTROWS ( FILTER ( 'Table', 'Table'[ID] = vID && 'Table'[LAST_STEP] = TRUE ) )
VAR vResult =
    IF ( vStatusApproved = vLastStepTrue, "OK", "NOK" )
RETURN
    vResult

Check your expected result for ID 333. It has three Approved rows and two True rows, hence NOK.

[SachinNandanwar]

Just incase if you want it in form of a table 

Table = ADDCOLUMNS (
    SUMMARIZE (
        Step,
        Step[LAST_STEP],
        Step[ID],
        Step[STATUS]
    ),
   "New_Column",
    VAR ApprovedCount = 
        CALCULATE (
            COUNTROWS ( Step ),
            ALLEXCEPT(Step,Step[ID]),
            Step[STATUS] = "Approved"
        )
        VAR StatusCount = 
        CALCULATE (
            COUNTROWS ( Step ),
            ALLEXCEPT(Step,Step[ID]),
            Step[LAST_STEP] = "TRUE"
        )

      RETURN IF (ApprovedCount=StatusCount,  "OK","NOK")
)

I never knew its possible to use variables in combination with Summarize. Learned something new.

[mickey64 · Accepted solution]

For your reference.

I add a 'New_Column' column below.

New_Column = IF(CALCULATE(COUNTX(FILTER('DATA','DATA'[STATUS]="Approved"),'DATA'[STATUS]),ALLEXCEPT('DATA','DATA'[ID]))=CALCULATE(COUNTX(FILTER('DATA','DATA'[LAST_STEP]="true"),'DATA'[LAST_STEP]),ALLEXCEPT('DATA','DATA'[ID])),"OK","NOK")

[DataInsights · Accepted solution]

@nok,

Try this calculated column:

New Column = 
VAR vID = 'Table'[ID]
VAR vStatusApproved =
    COUNTROWS ( FILTER ( 'Table', 'Table'[ID] = vID && 'Table'[STATUS] = "Approved" ) )
VAR vLastStepTrue =
    COUNTROWS ( FILTER ( 'Table', 'Table'[ID] = vID && 'Table'[LAST_STEP] = TRUE ) )
VAR vResult =
    IF ( vStatusApproved = vLastStepTrue, "OK", "NOK" )
RETURN
    vResult

Check your expected result for ID 333. It has three Approved rows and two True rows, hence NOK.