Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@Anonymous 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous we are pretty close, what defines first?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Probably the lowest Cosmos Shift ID - not sure the datatype of that field, so Value('Cosmos Shifts [ShiftID]), right?
@Anonymous 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?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |