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

Creating a table from a table visualization

Hello,

 

I have a table visualization that has a few columns based on DAX measures.

 

It has 500K records. 

 

I would like to export it to a CSV and would be happy to use the DAX stdio.

 

But...How do I "convert" the table visualization into a "regular" table to which I could query in the DAX studio?

 

Thanks,

Tamir

2 ACCEPTED SOLUTIONS

Sure, you would go into Modeling tab and then do a New Table. But, I would need to understand your source data and what you are doing in your table visualization. But, in theory it should just be something that uses the SUMMARIZE function.

 

https://msdn.microsoft.com/en-us/library/mt163696.aspx


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Anonymous
Not applicable

Thank you!

 

That is precisely what I needed.

I did:

 

CostTable = SUMMARIZECOLUMNS(factsSalaries[Employee ID], factsSalaries[Employee Name],dimCalendar[MonthYear], "Cost USD", [TotTSAUSD])

 

and then with DAX Studio could extract the table into a CSV file.

(http://biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax...)

 

Much appreciated,

Tamir

 

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User IV
Super User IV

Why can't you just click on the ellipses (...) and select "Export data"?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Because of the 30K limitations. I have 500K records

You might want to check out the Power BI REST API:

 

https://msdn.microsoft.com/en-us/library/mt147898.aspx

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Due to Organisational restriction, I am afraid it is not an option...

 

Is there a way to do it with DAX? something like a virtual table?

 

Sure, you would go into Modeling tab and then do a New Table. But, I would need to understand your source data and what you are doing in your table visualization. But, in theory it should just be something that uses the SUMMARIZE function.

 

https://msdn.microsoft.com/en-us/library/mt163696.aspx


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Can we create a virtual/dynamic table and use it in measures in excel using DAX? Appreciate your response.

@mvalaboju - Yes, I do it all the time. You just specify a VAR and set that equal to your table equation/filter. Then you can use it within a measure.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you Greg for the swift response. Can I use VAR in "PowerPivot for excel"/Data Model window in calculation area(measures) section? Also, is this virtual table available in pivot table to present the data? could you please also give some examples. Thank you.

I don't do much in PowerPivot in Excel but if it is DAX code, then yes, you can use VAR. Now, actually feeding that dynamic table to a pivot table, that's probably not supported as I know you can't do that in Desktop even though I am a huge fan of allowing Table and Matrix visualizations to accept a Measure that returns a table as output. So, for example, I do a lot of this kind of thing:

 

Measure = 
VAR __table = FILTER('SomeTable',[Column] = "some value")
RETURN
MAXX(__table,[Some Column])

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I am working on the solution in Excel. Thank you Greg for your help.

Hi Greg,

 

Please help me with the following:

how to show underlying data in power bi matrix visual that is based on conditional measures

 

Thank you.Test.PNG

 

Thank you Greg for the swift response. Can I use VAR in "PowerPivot for excel"/Data Model window in calculation area(measures) section? Also, is this virtual table available in pivot table when I have to analyze/present the data in excel?

could you please also give some examples. Thank you.

Thank you Greg for the swift response. Can I use VAR in "PowerPivot for excel"/Data Model window in calculation area(measures) section? could you please also give some examples. Thank you.

Anonymous
Not applicable

Thank you!

 

That is precisely what I needed.

I did:

 

CostTable = SUMMARIZECOLUMNS(factsSalaries[Employee ID], factsSalaries[Employee Name],dimCalendar[MonthYear], "Cost USD", [TotTSAUSD])

 

and then with DAX Studio could extract the table into a CSV file.

(http://biinsight.com/exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax...)

 

Much appreciated,

Tamir

 

View solution in original post

Oh yeah. Good point!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors