Forum Discussion
Three relationships between two tables
Dears,
I have two tables that contain following information.
| Date | Region | Country | SUPPly |
| 01/01/2016 | AFRICA | Angola | 100 |
| 01/02/2016 | AFRICA | Congo | 200 |
| 01/03/2016 | AFRICA | Algeria | 300 |
and
| Date | Region | Country | Product type | Supply |
| 01/01/2016 | AFRICA | Angola | A | 20 |
| 01/02/2016 | AFRICA | Angola | B | 30 |
| 01/03/2016 | AFRICA | Angola | C | 40 |
I need to create 3 relationships: 1) between dates 2) between region 3)between countries)
The calculation I need is for each product type, region and country I need to find =supply(Table 2)/supply(Table 1)
I know we can only have one active relationship at a time. I have managed to make some progress with index column, userelationship functions. However, they filter out nicely in Table visual. I need a line chart with date axis that would show me =supply(table 2)/supply(table 1) with region, country and product type slicers.
Example. Region = Africa, Country = Angola , product type = B
= 30/100
I have 7 regions, each has many countries. Date, Region and country columns are the same for both tables.
New to Power BI, would appreciate a thorough step by step instructions.
Thank you!
Nargiz
Hi Nargiz12345
The relationships are not appropriate. Instead of creating relationships between two Supply tables directly, you can add Dim tables to the model, then create relationships between Dim tables and two Supply tables. This will make the calculation easier. You just need to create a simple measure as below.
Result = DIVIDE(SUM(Table2[Supply]),SUM(Table1[Supply]))Ensure the Dim tables cover all possible values. And use columns from Dim tables in all slicers. I have attached the sample file at bottom. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
2 Replies
Nargiz12345 , You need to create two/three common dimensions: country/ region, Date
example
region= distinct(union(distinct(Table1[region]),distinct(Table2[region])))
region= distinct(union(distinct(Table1[country]),distinct(Table2[country])))
Join them with both tables and analyze together with common dimensions
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.Hi Nargiz12345
The relationships are not appropriate. Instead of creating relationships between two Supply tables directly, you can add Dim tables to the model, then create relationships between Dim tables and two Supply tables. This will make the calculation easier. You just need to create a simple measure as below.
Result = DIVIDE(SUM(Table2[Supply]),SUM(Table1[Supply]))Ensure the Dim tables cover all possible values. And use columns from Dim tables in all slicers. I have attached the sample file at bottom. Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
