DAX Help

[KimberlyF]

I have two fact tables - Cosmos Shifts and Care Attend.
I need to do a cross check and return columns from the Cosmos table if the Care Attend shift made it to Cosmos.

Essentially if the Care Attend [ShiftID] is found in the Cosmos [ExtID] then I need the resulting Cosmos Shift ID, ctdDate, and DW Created Time.

I'm having issues because of duplicate and blank values. I'm probably making this more complicated than it needs to be!

First I tried to do a LookupValue from the Care Attend table, but I couldn't make it work because of the duplucates... it's possible for one Care Attend shift ID to have multiple resulting Cosmos Shift IDs... but I just need to know if it made it over - i.e. the first one.

Then I tried to create filtered/calculated tables w/ the fields I need, and then join them. Problem is neither is showing a distinct list of Shift or ExtID

Cross Audit = 
 VAR Cosmos =                           
        CALCULATETABLE(
                    DISTINCT(
                        SELECTCOLUMNS('Cosmos Shifts',
                        "Cosmos Shift ID", 'Cosmos Shifts'[ShiftID],
                        "Shift ID", 'Cosmos Shifts'[extId] & "",
                        "ctdDate", 'Cosmos Shifts'[ctdDate],
                        "DWShiftCreateTime", 'Cosmos Shifts'[DWShiftCreateTime],
                        "Cosmos Source", 'Cosmos Shifts'[src],
                        "In Time", 'Cosmos Shifts'[inTime] +0
                    )),
                    CONTAINSSTRING('Cosmos Shifts'[src], "CLLTRK"),
                    Not CONTAINSSTRING('Cosmos Shifts'[extId], "TCN")
        )
                
VAR CAFilter = 
        Distinct( 
            SELECTCOLUMNS('Care Attend',
            "Shift ID", 'Care Attend'[Shift ID] & "",
            "In Time",  'Care Attend'[Submitted DateTime] + 0,
            "Status", 'Care Attend'[Status]
        ))

VAR Result = 
        NATURALLEFTOUTERJOIN(CAFilter,Cosmos)


Return
Result   

The outcome is close, but again has duplicate Shift IDs

Any  help is welcome! Thank you.

[parry2k]

@KimberlyF it is a bit tough to say yes/no or provide a solution. How about if you put together a pbix file with sanitized sample data and share it using one drive/google drive and also share the expected output, it will help to provide the solution.  

[parry2k]

@KimberlyF we are pretty close, what defines first?

[KimberlyF]

Probably the lowest Cosmos Shift ID - not sure the datatype of that field, so Value('Cosmos Shifts [ShiftID]), right?

[parry2k]

@KimberlyF You have to answer your own question, what happens if there are duplicate records, what value you will bring to the care attend table. If I understood correctly, you don't only want to know if there is a record in the Cosmos table but also bring some columns from the Cosmos table, correct?

[KimberlyF]

Hi Parry,
If there are duplicate records, essentially I just want to return the first one... I want the resulting columns if it is the first instance of that shift ID in the cosmos table. Essentially all others are adjustments.