topic Re: Checking for multiple Ids in DAX Commands and Tips

Checking for multiple Ids

Silvard — Mon, 09 Sep 2024 12:21:03 GMT

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 JobID can contain many ApplicationID's. I need a calculated column that checks if a candidate (ApplicationId) has commenced and under that same JobId, check if there are other candidates found suitable who are still going through the stages of offer and onboarding. If there aren't, the job is considered complete. This is Recruitment based.

I have tried the below but it doesn't give the correct result.

I greatly appreciate your help!

IsJobArchivable = VAR CurrentJobID = 'Fact Table'[JobID] VAR HasIncompleteNotOffer = CALCULATE( COUNTROWS('Application Dimension'), FILTER( 'Application Dimension', 'Application Dimension'[JobID] = CurrentJobID && 'Application Dimension'[Application Stage] <> "Offer" && NOT 'Application Dimension'[Complete Application] ) ) VAR HasComplete = CALCULATE( COUNTROWS('Application Dimension'), FILTER( 'Application Dimension', 'Application Dimension'[JobID] = CurrentJobID && 'Application Dimension'[Complete Application] ) ) RETURN IF(HasComplete > 0 && HasIncompleteNotOffer = 0, TRUE(), FALSE())

Re: Checking for multiple Ids

Selva-Salimi — Mon, 09 Sep 2024 12:31:41 GMT

hi Silvard ,

You can write the calculation as follows:

JobStatus =
VAR CurrentJobID = FactTable[JobID]
VAR CandidatesCommenced =
CALCULATE(
COUNTROWS(FactTable),
FactTable[JobID] = CurrentJobID,
RELATED(DimApplication[Status]) = "Commenced"
)
VAR CandidatesInOfferOrOnboarding =
CALCULATE(
COUNTROWS(FactTable),
FactTable[JobID] = CurrentJobID,
RELATED(DimApplication[Status]) IN {"Offer", "Onboarding"}
)
RETURN
IF(
CandidatesCommenced > 0 && CandidatesInOfferOrOnboarding = 0,
"Completed",
"Not Completed"
)

make sure to update the status based on your's.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.

Re: Checking for multiple Ids

Silvard — Mon, 09 Sep 2024 22:25:44 GMT

Hi Selva,

Thanks your prompt response.

I have tried the above but am getting the below error:

True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

How can we rewrite the formula please?

Re: Checking for multiple Ids

Silvard — Mon, 09 Sep 2024 23:02:45 GMT

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"

Re: Checking for multiple Ids

powerbiexpert22 — Tue, 10 Sep 2024 00:18:33 GMT

Hi Silvard ,

please try below ,please see below pbix file for reference

https://drive.google.com/file/d/1bTAgptmF5RFZQ4jwQdg3CrY77rWcTFTS/view?usp=drive_link

completed jobs =

CALCULATE(

COUNTROWS(

RELATEDTABLE(application_dim)),

application_dim[completed]="Y" && application_dim[stage]<>"Offer")

Re: Checking for multiple Ids

Silvard — Tue, 10 Sep 2024 03:25:41 GMT

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.

Re: Checking for multiple Ids

Silvard — Tue, 10 Sep 2024 03:56:01 GMT

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?

Re: Checking for multiple Ids

powerbiexpert22 — Tue, 10 Sep 2024 05:03:18 GMT

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).

Re: Checking for multiple Ids

Silvard — Tue, 10 Sep 2024 20:51:06 GMT

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