Forum Discussion

Silvard's avatar
Silvard
Resolver I
1 year 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's avatar
    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

     

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
1 year ago

Hi Silvard ,

You already have value "Success" available in Onboarded column as shown below, use this value to filter rows in your calculation. if possible , please send me the pbix file with sample data (attach in google drive and share the link). 

 

 

 

 

Silvard's avatar
Silvard
Resolver I
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