cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power BI - How to merge DAX measures to a table so all appears in a single matrix visual?

Hi,

I have a data table, see below:
Depot SRC.PNG

 

When dragging the above value into a matrix visual it shows the below matrix:

Depot table.PNG

The A_Depot and B_Depot columns are from the Category column in the source table. I've applied a filter using DAX to restrict MTDValue as A_Depot, and restrict MTDTarget as B_Depot.

 

All is fine so far.
I have some measures, created in DAX, that use a lookup table to provide some context filtering. The lookup table is:

Measure Lookup.PNG

When using the above Row Headers column (although in this end result these are row columns headers - just drag the 'Row Headers' field from Rows to Columns) and the ACategory or BCategory columns as context filtering - I get the below result:
Depot Measures.PNG

This looks fine too, however I would like to merge the immediately above table onto the end of the first table so to produce the below result:
EndResult.PNG

To get the above image I had the first table overlap the second table.

This won't do as the final solution as the number of Depot (i.e. Birmingham, Dudley, etc..) will likely be in the hundreds so when the user scrolls through the matrix visual I need both the table data and measures data to scroll as one.

 

How can I get the measures to merge onto the end of the data table horizontally?

I would preferably like to do this using DAX over doing it in Power Query as doing it in the latter could well screw-up the status-quo.
What is the best practice to achieve what I want and how?
Should I be looking to use 'AddColumns' or something similar?

 

Thanks in advance.

 

1 REPLY 1
Microsoft v-cherch-msft
Microsoft

Re: Power BI - How to merge DAX measures to a table so all appears in a single matrix visual?

Hi @Anonymous 

You may try to UNION the lookup table's Row Headers column and the Grouping_Level column to a new table and drag it into the matrix's column.Then you may create a measure to get the values.

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors