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

     

Silvard's avatar
Silvard
Resolver I
1 year ago

I have created the below as a visual of the situation.

 

The scenario I have looks like below

 

JobID.     AppID.     Stage.     Onboarded

1.               1.           Offer

1.               2.          Onboarding

1.               3.         Onboarded  Success

2.                4.         Backgrounds

2.               5.          Offer

 

In JobID 1, there is one candidate onboarded. How do I pick up that there is at least one more application that were found successful and is at one of those stages?

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