Re: DAX - Convert Matrix Visualization to Query Ta...

jdperry · ‎07-25-2019

Hey all,

I have created a matrix visualization using measures, and I would like take the resulting data and use it elsewhere in my model. The matrix looks like this:

My MatrixI can export the visualization data to .csv, and re-import it, but that's obviously not a great solution. Is there any way to use DAX to create a new (un-pivoted) table using this data? I'll note that the "Apportioned Labor Billings" measure only calculates the correct figures when presented in this matrix (the "correct" calculation that I need depends on the number of staff in each position in any given month, as presented in this matrix).

Any help would be much appreciated! Thanks!

HarishKM · ‎04-05-2021

@jdperry Hey .
I will suggest Kindly use Analysis in excel feature of power bi then you can use these same measure and create a new chart on top of that . That will be the best approch for your problem.
Once your data is updated / refreshed in power bi report your excel report will also get updated .Never the less you have to hit refresh now button in data tab.

Thank you

Anonymous · ‎04-05-2021

I ran into a similar problem, @parry2k how do i take the data inside a matrix and make it a data query??

parry2k · ‎07-25-2019

@jdperry you can surely create another table from your matrix but would like to know the purpose of creating new table? What you are trying to achieve?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

jdperry · ‎07-26-2019

I have a series of costs set out in different tables, and at the end of the day I'm going to need to prepare a cost summary by month using the data in those tables. Some of the tables I can use as-is, and the values in others (like the labor costs data I show in my example) have to be adjusted according to a formula. For example, the measure I'm using here for my adjusted labor billings is:

Apportioned Labor Billings = IF('Measures Table'[Total Staff]>=3, 
'Measures Table'[Total Staff Billing]*'Measures Table'[Apportioned Percentage],'Measures Table'[Total Staff Billing])

So what I would like to do is to create a new table from this matrix, and sum these values (sum of Apportioned Labor Billings per month) with monthly values from the other tables to create a total cost per month figure.

Hope that makes sense! Thank you for your help!