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

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.

Reply
keelym
New Member

Relationship only working for one month?

Hi,

 

I have a data set that has monthly KPI data for various cost centers and their respective managers. Cost centers are often moved around to different managers, so I wanted this represented. To do this, I have a hierarchy table that contains the month, cost center, and managers of that cost center for a given month. Then, I created helper columns in the KPI table and hierarchy table by concatenated cost center and the date and made a many-to-one relationship.

 

keelym_1-1649709505412.png

 

 

keelym_3-1649709825834.png

 

 

I thought this would work, and it did at first. Then I refreshed the data for this month, and now the connection only works for March? Is there a different I should be doing that ties cost centers to their managers? I also have a date table, but you can only have one active date relationship so that is with the KPI table.

1 ACCEPTED SOLUTION
vanessafvg
Super User
Super User

have you tried to compare values that are matching and aren't?  clearly something has affected your join, i would dig down into why values outside of march are not joining.     Even looking at the details above the top table, your helper column looks like a different format.  Also do you maybe have white spaces?  maybe a trim or clean might help?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
vanessafvg
Super User
Super User

have you tried to compare values that are matching and aren't?  clearly something has affected your join, i would dig down into why values outside of march are not joining.     Even looking at the details above the top table, your helper column looks like a different format.  Also do you maybe have white spaces?  maybe a trim or clean might help?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Thought I was going insane for a minute but realized that dates on the manager table weren't going into powerbi as the first of the month, ie Jan-22 became 1/22/22 instead of 1/1/22. I fixed it and the report works again. Thank you!!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.