Forum Discussion
Helper IICreating a Measure that checks if a value exists on another table in a Date Range
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:
3 Replies
- v-chenwuz-msft
Community Support
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)
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]) inCALCULATETABLE(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..
