Creating a Measure that checks if a value exists on another table in a Date Range

[devesqdeves]

Hi!

I have 2 tables  , Onboarding and Starting, which doesn't have a direct relationship. But using USERELATIONSHIP using both CandidateId i can join them in the measure.

NOTE : Both of the tables are connected to DimDate ( Calendar Date ) using the StartDate of each table.

My goal is to create a measure/calculated Column (Exist Project) on the Onboarding table that gives 1 if the CandidateId exists in Starting Table in the selected Week OR IN  A WEEK GREATER THAN the selected week, and gives 0/blank if it doesnt exist in the selected Week OR IN  A WEEK GREATER THAN the selected week. Basically, in the selected week or in a Date after that , it just cannot be before that selected week.

Here's a example of the visual. Now it is 0 because it is giving the wrong value, because i checked and most of the CandidateId on Onboarding exist on Starting Table.

The field that i am using in the Week Filter is WeekOfYear that gives the week number in the year.

I am using 2 measures:

CheckProject =

IF (

    CALCULATE(

    CALCULATE(DISTINCTCOUNT('DW FCTOnboarding'[CandidateId]),FILTER('DW FCTStartings','DW FCTStartings'[CandidateId]=

        LOOKUPVALUE (

        'DW FCTOnboarding'[CandidateID],

        'DW FCTOnboarding'[CandidateID],'DW FCTStartings'[CandidateId]))),USERELATIONSHIP('DW FCTStartings'[CandidateId],'DW FCTOnboarding'[CandidateId]))

        <> BLANK (),

    1

)

ExistProject = if('DW FCTOnboarding'[ExistProject]=1,"1","0")

Thank you for your time!

[v-chenwuz-msft]

Hi @devesqdeves ,

Your measure is a bit complicated. Try using in or CONTAINS.

CONTAINS function (DAX) - DAX | Microsoft Learn

A table visual with CandidateId field from 'DW FCTOnboarding' and a measure like the following.

measure = 

if( selectedvalue( 'DW FCTOnboarding'[CandidateId]) in values('FCTStartings'[CandidateId]) ,1,0)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[devesqdeves]

@v-jingzhang , @lbendlin (the other post was confusing, its the same problem)

@v-chenwuz-msft , 

Hello , thanks for the input!

It works for the week part, but the problem is that i need to know for the selected week or with a Date greater than that week. It has to be in that week or in weeks after, cannot be before.

If a candidate has the startdate 28/09/2022 and i have the week 38 selected ( ends in 24/09/2022),

it still has to show value 1 in that measure, because its a date after the last day of the selected week. What matter is if it is in that selected week or after.

Note: I know i have to use disconnected tables ( right?) but it is a bit complex for me and i do not understand how to create a measure that overwrite the selected week filter.

[devesqdeves]

I tried this :

ExistProjectMeasure =

 if(ALLSELECTED( 'DW FCTOnboarding'[CandidateId]) in

CALCULATETABLE(VALUES('DW fctstarting'[CandidateId]),FILTER('DW FCTStarting','DW FCTStarting'[StartDate]>MIN('Date'[Date])))

 ,1,0)

Update: For what ive seen and searched , the problem is on the part before the IN function , its not selecting all the CandidateId from Onboarding when comparing with CALCULATETABLE , it is only selecting the CandidateId within the selected Week..