Data Model Relationships Not working as expected?

I'm getting started trying to understand Data Modeling and Power Pivot more. I'm not sure if it will make things easier in the long run for me or not vs using various Xloopups, Index/Matches, etc but at least trying to start learning it.

So I have one small table with 3/4 columns in a WorkSheet

Those are Company Number | Cost Center | Legal Owner | Company Name

And I copied that data into 2 separate worksheets just deleting 1 column in each. So I have
In Sheet 1, the first 3 columns
In Sheet 2, removed Legal Owner and replaced with Company Name

So Company Number and Cost Center exists in both tables and are identical.

I then added both Sheet 1 table and Sheet 2 table to the model and created a relationship between the 2 tables using Company Number.

But in the pivot table, shouldn't I then be able to use the Company Number from Table 1, and get the Company Name from the other table?

I will sanitize and post a sample soon But is the above steps correct?

Thanks,
PT

EDIT: Sample Attached

Because of the direction of the relationship (filter) only go from Table2 to Table1.
I'm not sure how to edit the relationship in Power Pivot as there is no option to select.

In Power BI there is an option to select Cardinality to 1:1 and direction to Both.
Both.png

If all you do is put the Company Number from Table 1, and the Company Name from the other table in the Rows area then you'll get a complete distinct list of Company Names listed with each Company Number.
However if you have a value based field, such as Revenue, then when that is added in the Values area the pivot table will only display the rows in which there is a value as modeled.
Let us know if you have any questions.

I'm back trying to understand better how the relationships work in Data Models, I thought this should be working.

In my model, I have identical tables for Actuals, Budget and Forecast and have tables for each of the last 3 years. Those 9 tables all have the same column fields.

I then have a separate Divisions table that includes the Company Number, Company Name, City, State, etc.

And I created a 1 to many relationship between "Company Number" and "SiteTag" to each of the 9 tables. And those columns are text fields in tables.

Now when I make a Pivot table with that Model, I list Company Name (Division's Table) in rows, and use description (from any table) as a filter. I then try and get the totals for the 'Amt' fields from each table.

But when I change the description, the totals will only update for that table only. The other table "Amt" fields do not change with it?

The file of just the model is over 30MB already and to big to share. But below is a screenshot of the relationships.

I just can't figure out where am I going wrong.

2021-01-08_15-42-02.png