Forum Discussion

bvy's avatar
bvy
Icon for Helper V rankHelper V
5 years ago

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.

 

Need Help

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      bvy
      Icon for Helper V rankHelper V
      5 years ago

      > 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's avatar
        lbendlin
        Icon for Super User rankSuper User
        5 years ago

        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.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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]
)
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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