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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bvy
Helper V
Helper V

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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