Creating Measures without common relationship??

[ashmitp869]

Hi, 

I've 2 tables with no relationship between them, I would like to create a measure to add one column Qualified in table DSR by checking the Timesheet Date with the Date Start and Date End.

will take the red one not yellow 

Output result will be like below image

Trying to implement this below logic

Qualified =

CASE WHEN Timesheet Date >=Date Start && Timesheet Date <= Date End Comments contains "L" then "Learner"

         WHEN Timesheet Date >= Date Start && Timesheet Date <= Date Expiry then "Qualified"

         WHEN Timesheet Date >=Date End && Date Expiry = null then "Not Qualified"

         WHEN Timesheet Date < Date Start then "Learner"

Notes : Multiple rows in DSR_DNQ and mutiple rows in Qualifications Table thus I avoid to do merge DSR with Qualifications as it duplicating.

I've included the pbix :

https://github.com/suvechha/samplepbi/blob/main/samplefile.pbix

Thanks in advance

[danextian]

Hi @ashmitp869 ,

This is a sample calculated column formula based on the data provided.

Qualified = 
VAR __TSD = DSR_DNQ[Timesheet Date] --Timesheet Date
VAR __CODE = DSR_DNQ[Employee Code] -- Employee Code
VAR __RESULT =
    SWITCH (
        TRUE (),
        CONTAINSSTRING (
            CALCULATE (
                MAX ( Qualifications[Comments] ),
                FILTER (
                    ALL ( Qualifications ),
                    __TSD >= Qualifications[Date Start]
                        && __TSD <= Qualifications[Date End] && Qualifications[Employee Code] = __CODE
                )
            ),
            "L"
        ), "Learner"
    )
        
RETURN
    __RESULT

I couldn't validate it against your sample result as the sample data in the result screenshot doesn't match what's in the pbix.  Your screenshot show 5 April as the earliest date but your sample pbix shows 23 May. Also, the above formula is missing the second, third and fourth case condition. The are many matching rows using the logic provided.

Please refer to the screenshot below. Using the second condition, there are many rows that are have a starte date on or after 23 May 2022 and with null expiry date. So which one should be picked?

[ashmitp869]

Hi @danextian ,

The screenshot you provided marked red - will not pick up as the Skill Code not matches with Qualification ID of second table.

The table DSR will be like - it should check the Key with the second table key 

if matches then

pick the Timesheet Date and check with Date Start and Date End condition

The Condition will only work for 

OBRIJA-1

OBRIJA-3

OBRIJA-8

OBRIJA-22 ( dont have in second table thus NQ)

1

All the OBRIJA-8 take all timesheet date from dsr but will check the red one date period this will be Learner

I tried to use you dax formula but it didn't work