Forum Discussion

Nargiz12345's avatar
Nargiz12345
Regular Visitor
4 years ago
Solved

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 ...
Need Help
Show and Tell
Tutorial Requests
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

     

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

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.