power bi union

Anonymous , 

Create a calculated table for each fact table with a common structure. For example:

DAX
SalesTable =
SELECTCOLUMNS (
'Sales',
"Location", 'Sales'[Location],
"Product", 'Sales'[Product],
"Type", "Sales",
"Quantity", 'Sales'[Quantity]
)

SalesContractsTable =
SELECTCOLUMNS (
'SalesContracts',
"Location", 'SalesContracts'[Location],
"Product", 'SalesContracts'[Product],
"Type", "Sales Contracts",
"Quantity", 'SalesContracts'[Quantity]
)

PurchaseContractsTable =
SELECTCOLUMNS (
'PurchaseContracts',
"Location", 'PurchaseContracts'[Location],
"Product", 'PurchaseContracts'[Product],
"Type", "Purchase Contracts",
"Quantity", 'PurchaseContracts'[Quantity]
)

InventoryTable =
SELECTCOLUMNS (
'Inventory',
"Location", 'Inventory'[Location],
"Product", 'Inventory'[Product],
"Type", "Inventory",
"Quantity", 'Inventory'[Quantity]
)

Create a new table that unions all the calculated tables:

DAX
CombinedTable =
UNION (
SalesTable,
SalesContractsTable,
PurchaseContractsTable,
InventoryTable
)

Create measures to calculate the totals for sales and ownership:

DAX
SalesTotal =
CALCULATE (
SUM ( CombinedTable[Quantity] ),
CombinedTable[Type] IN { "Sales", "Sales Contracts" }
)

OwnershipTotal =
CALCULATE (
SUM ( CombinedTable[Quantity] ),
CombinedTable[Type] IN { "Purchase Contracts", "Inventory" }
)

Hi Anonymous ,

Did the reply bhanu_gautam offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Thank you for your understanding.

Best regards,

Lucy Chen