4

I have an excel file that has a table imported from a txt in a sheet (using New Query). From that table I created a pivot table and some formulas like for example MAX().

I was told that for large files it is better to add the info to the data model as connection only (the data is not visible in a sheet).

No problem in creating the pivot and works great, but trying to do the formulas excel does not find the Table.

Before I could do something like this:

=+MAX(Table1[@[Column1]])

but know when I do MAX, the system does not find the Table1 I have loaded as connection only. Is there any way to relate a formula to data that has been added to the model as connection only?

Thanks.

RADO
  • 7,733
  • 3
  • 19
  • 33
mmtzdesalnas
  • 41
  • 1
  • 2

2 Answers2

4

In order to access data in the data model you can use "Cube functions" you can follow these steps:
1- From inside the "Manage Data Model" option, create a pivot table of your table
2- Customize the new pivot table according to your needs
3- Click the ribbon "PivotTable Tools" | "Analyze" | "OLAP Tools" | "Covert to formulas"
4- Optional: Merge the formulas in one

Remarks:
The functions (MAX, SUM, Etc.) must be defined in the Pivot Table.

Here is an screencast I created for you. enter image description here

Reference: https://support.office.com/en-us/article/cube-functions-reference-2378132b-d3f2-4af1-896d-48a9ee840eb2

Ricardo Diaz
  • 5,658
  • 2
  • 19
  • 30
  • Hi, thanks! for the help. I understand then that there is no way to do a vlookup in all the data if this is not included in the pivot table? The information I was trying to get in the formulas is not included in the Pivot Table. – mmtzdesalnas Feb 03 '19 at 18:32
  • The pivot table is just a reference to the original table. So you just have to include the columns you need to work with. Then, as you see in the screecast, you can erase the pivot table cells. – Ricardo Diaz Feb 04 '19 at 01:00
  • Thank you for this great post, it makes accessing the data model that much easier with formulas, even on another table we can use that just as a normal fomula and its fast which is good, which changes everything when doing lookups, a index match this way I like, thank you – kire38 Sep 11 '20 at 08:26
0

First Create a Data Model table then use

=MAXIF(Table1[Values],Table1[Labels],"a")
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Vivek
  • 1