Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
djm7
Frequent Visitor

power bi union

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

 

2 ACCEPTED SOLUTIONS
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

v-xinc-msft
Community Support
Community Support

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

View solution in original post

3 REPLIES 3
djm7
Frequent Visitor

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:

v-xinc-msft
Community Support
Community Support

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.