Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

DAX Help

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.

5 REPLIES 5
parry2k
Super User
Super User

@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.

parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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

parry2k
Super User
Super User

@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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.