Creating a table from a table visualization

[Anonymous]

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

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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

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

[Anonymous]

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?

[Greg_Deckler · Accepted solution]

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

[Anonymous]

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

[Greg_Deckler]

@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.

[Anonymous]

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.

[Greg_Deckler]

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])

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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]

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 · Accepted solution]

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

[Greg_Deckler]

Oh yeah. Good point!