Relationship only working for one month?

[Anonymous]

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.

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.

[vanessafvg · Accepted solution]

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?

[Anonymous]

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!!