Help linking tables please!

[JD2301]

Hi all,

I'm pretty new to BI so looking for some help with what I think should be a simple task..

I have a 'Dispensations' table that shows a sites unique ID.
I have a 'Timebase' table which shows the site name.
I need to get the site name from the 'Timebase' table into the the 'Dispensations' table so I can refrence both the site and the site unique ID (along with a few other things).

This sounds simple and I imagine it is so please could anyone help me out? I've never used the measures before!

TIA.

JD.

[MattAllington]

you shouldn’t be looking to bring the name into the other table. You should join them on a common ID column, then they will work as if they were one.  Have a look at my article here. https://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

[JD2301]

Hi Matt,

Thanks for the reply and the info on the article, much appreciated.

The below info is from my dispensations table:



The 'Sin Format' column is the unique site ID.

Below is the info from my Timebase table:



What I am trying to do is filter the 'Dispensation Status' column in the Dispensations table to show only 'Temporary Dispensations'. There should be 168. I can do this fine so that the table shows 168 'Sin Format' numbers only. However there is no site name stated in this table so you are unable to see which number relates to which site. The site name is shown in the Timebase table in the 'Site Worked' column. 
When I drag the 'Site Worked' field into the table, for some reason instead of showing 168 sites, the table only shows about 50 instead of 168.

Any ideas why it is only showing some but not all the sites?

Thanks.
JD

[MattAllington]

OK, so this is a data modelling problem and your original idea is the right approach.  But what you really should be doing (as part of a better data design) is to have all the non changing data in the lookup table (Dimension table) and all the changing/transactional data in a data table (fact table).  Currently your data table contains the name in the data table.

I suggest you use Power Query (edit the queries that load your data).  From there you can

1. create a table (using the second table you posted) that contains just the site worked sin and the site worked column and remove duplicates.
2. starting with the first table, merge the table from 1 above and extract the site worked column into the table
3. remove the site worked column from the second table

This is the "correct" way to do it, but it is also work and you may not be bothered, depending on the bigger picture.  If you prefer an easier approach that is not best practice (which is fine if you do), then simply do the following (in Power BI, not Power Query).

1. click on the dispensations table
2. add a new column
3. you can type a formula =CALCULATE(SELECTEDVALUE(Timebase[Site Worked]))

I think that will work and bring the data into the lookup table.  The downside is the data is duplicated, but if that doesn't worry you, then just do it.

Another approach (also not best practice) is to turn on bi directional cross filtering in the model view.  This way you can just drag the column from the second table and it will filter the first table

[Anonymous]

does your timebase table also have the site Unique ID? If so, you can create a relationship between the 2 tables on this ID that will then allow you to present data from different tables.

-This is provided you don't have other relationships between this tables already.

If this is the case, I would consider creating a reference table with the ID and name as a standalone table and then creating seperate relationships.

-There is probably more complicated ways to do it, but these are the 2 simpler (longer maybe) options I would use in this case.