power bi union

[djm7]

I have a challenge that I need help with.

I have a power bi semantic model that contains information re orders, sales, sales contracts, purchase contracts, inventory etc in seperate fact tables.

the source data is in a ms analysis services database.  

I want to build a report that looks like this.

location     products         type                             quantity kg

toronto      abc                  sales                           100

toronto      abc                  sales contracts            250

sales total toronto                                                350

toronto      abc                   purchase contracts    300

toronto      abc                   inventory                   75

ownership total toronto                                       375

chicago      abc                  sales                           200

chicago      abc                  sales contracts            250

sales total chicago                                                450

chicago      abc                   purchase contracts    300

chicago      abc                   inventory                   25

ownership total chicago                                       325

it appears dax would union the base tables together and since they look nothing alike this would be a problem

since the source data is in analysis services, this I belive causes a problem for power query.

any ideas would be appreciated

[bhanu_gautam · Accepted solution]

@djm7 , 

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" }
)

[djm7]

I am not sure how I use these new measures in the matrix report I am building

Create measures to calculate the totals for sales and ownership:

[Anonymous · Accepted solution]

Hi @djm7 ,

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

[bhanu_gautam · Accepted solution]

@djm7 , 

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" }
)