Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

@Anonymous - 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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

Oh yeah. Good point!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.