How to UNION Tables containing a Measure using DAX or other

[bvy]

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.

[v-shex-msft]

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

[smpa01]

@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]
)
)

[lbendlin]

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.

[bvy]

> 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. 

[lbendlin]

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.