I wan to link multiple tables

[Saqibmughal00]

I got a situation, got planned data at different sheet and actual at different sheet. both are linked to date table but I also want to link them based on "adjusted scope" as the slicers are based on that. Shall appreciate your support on that. attaching screenshot below for reference. Force allocation data is actual file while planned is plan file and both have common adjusted scope. An i am keen on one to many relation (not sure if others will work as never tried).

[danextian · Accepted solution]

Hi @Saqibmughal00 

You can create a separate dimensions table to link the two tables just like you did with dates. You can do that either in the query editor or in DAX. In DAX, try this:

DimScope =
VAR __FORCE =
    SELECTCOLUMNS ( 'Force Table', "Adjusted Scope", 'Force Table'[Adjusted Scope] )
VAR __PLANNED =
    SELECTCOLUMNS ( 'Planned', "Adjusted Scope", 'Planned'[Adjusted Scope] )
RETURN
    DISTINCT ( UNION ( __FORCE, __PLANNED ) )

[Saqibmughal00]

Thanks, Thats what exactly I did and it sorted the problem

[RossEdwards]

It looks like Adjusted scope is just a text field?  I would suggest you need an adjusted scope table and join it to both tables, like you did with date.

The easiest way to do this would be in Power Query.

1. New query using "Reference" to the query "Force Allocation Data..", remove other columns except for Adjusted Scope.  Mark this query as "Enable load" False
2. New query using "Reference" to the query "Planned", remove other columns except for Adjusted Scope.  Mark this query as "Enable load" as False
3. Append both into a single table.  Use the "Remove Duplicates" to get a distinct list of Adjusted Scope.  Import that table into your model
4. Join both tables to your new adjusted scope table.
5. Ma