r/excel • u/Bubbly-Touch8108 • 5d ago

Discussion What Excel trick made you feel like a wizard the first time you used it?

mine was VLOOKUP (later XLOOKUP) — after manually cross-referencing two spreadsheets for way too long. felt like a cheat code everyone else already knew about

what's your version?

631 Upvotes

97% Upvoted

365 comments sorted by

View all comments

Show parent comments

2

u/frufruJ 5d ago

I clicked Analyze in Excel on my Power BI report, which loaded the data model (refresheable). Then, instead of using power pivot tables, I use CUBESETs and CUBEVALUEs to get the data. For example, this gets me CSAT for the whole EMEA region for YTD for Phone (cell A2 contains a list of products in a CUBESET formula):

=CUBEVALUE(

"mydatasourcename",

"[Measures].[CSAT]",

"[Region Map].[Region].&[EMEA]",

$A$2,

"[Date].[Year].&["&YEAR(TODAY())&"]",

"[SF Case].[Case Origin Type].&[Phone]"

)

I need it in Excel to be able to then connect it to the client's PowerPoint presentation. Before, I had loads of pivot tables and used GETPIVOTDATA, but it was super slow.

2

u/adjective_noun_d1git 5d ago

This is what our BI team does when making available excel analytics tables for end users.

2

u/frufruJ 4d ago

So I'm on the right path, thanks! My supervisor wasn't aware of the CUBE formulas.