Forum Discussion

Saqibmughal00's avatar
Saqibmughal00
Icon for Helper I rankHelper I
2 years ago
Solved

I wan to link multiple tables

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 ...
General Comment
Need Help
Show and Tell
Tutorial Requests
  • danextian's avatar
    danextian
    2 years ago

    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 ) )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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