Forum Discussion

Resolver I

2 years ago

Solved

Checking for multiple Ids

Problem: I need to identify completed JobId's, ideally as a calculated column. I have 1 fact table with JobID and ApplicationID and two dim tables, one for JobID and one for ApplicationID. Each Job...

Silvard

1 year ago

Thanks to gmsamborn for suggestion the below solution - I only needed to change under VAR _NotCommenced the Application Dimension[Status] to IN {Offer, Background Checks, Onboarding}

IsJobArchivable = 
VAR _Job = [JobID]
VAR _Commenced =
    COUNTROWS( 
        FILTER( 
            'FactTable',
            'FactTable'[JobID] = _Job
                && RELATED( 'Application Dimension'[Status] ) = "Commenced"
        )
    )
VAR _NOTCommenced =
    COUNTROWS( 
        FILTER( 
            'FactTable',
            'FactTable'[JobID] = _Job
                && RELATED( 'Application Dimension'[Status] ) <> "Commenced"
        )
    )
VAR _Result =
    IF(
        _Commenced > 0
            && _NOTCommenced = 0,
        TRUE(),
        FALSE()
    )
RETURN
    _Result

Silvard

Resolver I

2 years ago

I updated the formula using relatedtable instead, but It's still not producing the expected result.

In fact another formula, much simpler, produces the same result.

ApplicationisCompleted =
IF(DimApplication[Status])<>"", Completed)

This column contains the candidates who have finalised their onboarding and otherwise "".

I somehow need a formula that checks this column and where there are "Completes", check the JobID for any other ApplicationID's that are going through the offer and onboarding stages.

This column is what equals the below in your formula.

RELATED(DimApplication[Status]) = "Commenced"