cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bvy
Helper III
Helper III

How to UNION Tables containing a Measure using DAX or other

Given a typical Sales table with dimensions of Product and Customer (joined in the traditional way, one-to-many on ProductID/CustomerID), suppose I have a measures on the Sales table called SalesOverLastWeek which shows, for a selected week, the percent increase/decrease of sales over the week before.

 

I can easily build a table in PowerBI, drag in CustomerName and the SalesOverLastWeek measure. I might have a slicer on WeekEnding somewhere, so we’re only looking at one particular week at a time. Then I would see:

 

WeekEnding: 2-Oct-2021

 

CustomerName | SalesOverLastWeek
GizmoProps | -0.7%
Joe’s Shack | 1.2%
Donna’s Cafe | 2.3%

 

Or I can drag in ProductName and do the same:

 

ProductName | SalesOverLastWeek
Widget | 1.1%
Gizmo’s | 0.4%
Red Paint | -2.1%

 

What I would like is a COMBINED table which allows me to union these (or more!) tables and shows me the following:

 

CategoryName | SalesOverLastWeek
GizmoProps | -0.7%
Joe’s Shack | 1.2%
Donna’s Cafe | 2.3%
Widget | 1.1%
Gizmo’s | 0.4%
Red Paint | -2.1%

 

There could also be (I suppose) a CatgegoryType column that gets introduced in the table creation step:

 

CategoryName | SalesOverLastWeek | CategoryType
Donna’s Cafe | 2.3% | Customer
Widget | 1.1% | Product

 

The addition of dimensions would not be dynamic necessarily – i.e. the CategoryType could be hard coded in DAX when the table is created since we know exactly what dimensions we want to pull in.

 

Thoughts on how best to achieve this? Thanks.

 

5 REPLIES 5
v-shex-msft
Community Support
Community Support

Hi @bvy,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
smpa01
Super User
Super User

@bvy  please adapt the following to your scenario and see if it gives what you were hoping for

 

UNION
(
Selectcolumns(
ADDCOLUMNS(VALUES(Customer[CustomerName]),"SalesOverLastWeek",[SalesOverLastWeekMeasure])
,"CAT",[CustomerName],"SalesOverLastWeek",[SalesOverLastWeek]
),
Selectcolumns(
ADDCOLUMNS(VALUES(Product[ProductName]),"SalesOverLastWeek",[SalesOverLastWeekMeasure])
,"CAT",[ProductName],"SalesOverLastWeek",[SalesOverLastWeek]
)
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


lbendlin
Super User
Super User

Have you considered that this (mixing dimension values) may be confusing for your users?

You can UNION tables in measures, but only as table variables, and only as intermediate steps, before returning a single scalar value.

Have you considered that this (mixing dimension values) may be confusing for your users?

 

No. This factors into a larger solution. Just need some DAX that will bring two tables together in the manner described. 

You cannot UNION  dynamic tables (ie including measures) into a materialized (static) calculated table.

 

You would need to look into Calculation Groups and add reference columns that identify which dimension the measure should calculate for.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.