Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
2 years ago

Creating Measures without common relationship??

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...
Need Help
danextian's avatar
danextian
Super User
2 years ago

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's avatar
    ashmitp869
    Responsive Resident
    2 years ago

    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