Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Solved! Go to 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" }
)
Proud to be a Super User! |
|
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
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:
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
@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" }
)
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |