Forum Discussion

Silvard's avatar
Silvard
Icon for Resolver I rankResolver 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's avatar
    Silvard
    2 years 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
Icon for Resolver I rankResolver I
2 years ago

Hi Powerbiexpert!

 

Thanks so much for helping.

 

Unfortunately this produces the same result as before.

 

Let me clarify the problem/scenario.

 

I have 3 tables - JobID Dimension, ApplicationID Dimension and lastly a FactTable that links these together and includes both JobID and ApplicationID, relationships one to many going to FactTable.

A JobID can contain many ApplicationID's. Think about it this way, a line area requests to recruit, which produces a JobID. Any candidates that apply, produces an ApplicationID.

 

Under the ApplicationDim I have a column that identifies all ApplicationIDs that have completed their onboarding. If an applicationId hasn't reached that stage, the result is "" in this column.

I also have another column, Stages, under this Dim that picks up the stage each application is at (there are about 9 stages candidates go through)

 

Where an ApplicationID is finalised (on boarded) I somehow need this question answered - are there any other ApplicationIDs under that same JobID that are ongoing an offer/onboarding stage.

  • Silvard's avatar
    Silvard
    Icon for Resolver I rankResolver I
    2 years 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
      Icon for Impactful Individual rankImpactful Individual
      2 years 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
        Icon for Resolver I rankResolver I
        2 years 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