Forum Discussion

Analyst26's avatar
Analyst26
New Member
1 year ago

Data modelling

Hi   i have two separate spreadsheets that I've loaded into power bi. Some of the columns have the same info ie month, department and product code. The other columns contain a value for a financial...
Need Help
danextian's avatar
danextian
Super User
1 year ago

Hi Analyst26 

You will need to create separate dimension tables for the common dimensions between the two and use measures to combine the values from both. These can be created either in DAX or M or loaded from the data source. Example DAX calc table:

Products =
VAR _tableA =
    SELECTCOLUMNS (
        TableA,
        "Product ID", TableA[Product ID],
        "Product Name", TableA[Product Name]
    )
VAR _tableB =
    SELECTCOLUMNS (
        TableB,
        "Product ID", TableB[Product ID],
        "Product Name", TableB[Product Name]
    )
RETURN
    DISTINCT ( UNION ( _tableA, _tableB ) )

 

Create a one to-many single direction relationship from Product ID in this table to Product ID/code columns of the other tables. Use this column in your visual - not from your fact table. Create a measure to combine the aggregation from both fact tables. Example:

Sales Table A and B =
SUM ( TableA[Sales] ) + SUM ( TableB[Sales] )