Power pivot/Excel - use of EVALUATE / SUMMARIZE/ etc... ?

[Lolo]

Hello,

I have some question about the scenarios on when and how to use the EVALUATE/SUMMARIZE function of power pivot.

Let me explain:
- I use a lot power query to crunch data in order to add a the wanted data/tables in the data model
- Then I use power pivot to manage the data model, and create basic measures
- Then i use Excel pivot tables or power view to display data

Very basic I would say.

But I can see now that there is another possibility by using EVALUATE or SUMMARIZE functions, like in these examples :

https://www.powerpivotblog.nl/imple...l-2013-using-dax-query-tables-and-powerpivot/
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

These function enable to create like view (they are equivalent of some SQL instrcutions), make some join from the data model and send the result as an excel table. It seems really interesting.

But actually, I don't figure out how to use these functions and concepts, on which scenario these concpets could be useful ?
* Why would I need to join tables, after having modeled my data into power query and added to the datamodel ? What is the interest ?
* Why would I use them and not for example doing the same job in a combination of power query and basic powerpivot ?
* When it is a good ide to use them ? Why ?

I really appreciate if someone has some experience on them and can report some feedbacks, because for now, for me, it is confusing

Hope I'm clear.

 

[Lolo]

I have found this :

Learn how to write DAX Queries

• It may not be immediately obvious, but once you build lots of business logic into your data model, there could be times when you just want to get a table (or list) of data and extract it to use for other purposes. You could use a pivot table for this, but it can be better to write a query over the data model and extract the data you need into a table in some instances – particularly if the table is large.

So I understand it is useful to extract datamodel to excel based on a simple or complex query (like in SQL query I would say), and use it for other purposes. OK...

If you have any extra info or scenario, or experience, it would be really appreciated.
For example can I use SUMMARIZE and EVALUATE in a powerpivot measure ?

 

[NARAYANK991]

Hi ,

Have you checked out this link :

http://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Narayan

 

[Chihiro]

DAX isn't just for PowerPivot and data modeling within Excel. It originates from SQL Server Analysis Services (SSAS) team and is partly an expansion/evolution of older Multidimentional Expression (MDX). Used in PowerBI, PowerPivot & SSAS to name a few.

SUMMARIZE and EVALUATE are especially useful in writing complex aggregation query within data model. For analyzing data using different measures and matrix.

Basically, it's used to aggregate table, without altering original data brought in. So that data can be visualized using both sets, to present/analyze data from many angle.

One such use case:

Pareto chart - It's useful chart for business analysis and can be used to quickly identify what business issue need attention etc. However, it isn't readily available in PowerBI/Excel.

See link for detailed tutorial on how to make this visual in PowerBI (same principle applies to Excel).
https://powerbi.tips/2016/10/pareto-charting/

 

[Lolo]

Dear Chihiro,
Actually I fully understand the interest of these functions, but it is just I didn't figure out how to use them in Excel/Power pivot.

In you link , with power BI Designer/desktop (tool I don't use for now), you can create a table based on these DAXfunctions.
OK it is great, but in Power pivot add in, in Excel, I don't see this ability (no Button 'create table' to put a DAX expression). That is why I was confused on how to use them.

So the question is finally : Is it possible to use these function directly in the Excel power pivot add in ?

 

[Chihiro]

Is it possible to use these function directly in the Excel power pivot add in ?

Not without putting it back onto Excel sheet first. You can then load the transformed data back into PowerPivot data model.

It's technique often used to create dynamic date dimension table, etc. Although in most instances, you can do the same or similar in PowerQuery (albeit more unwieldy for dynamic date dimension table).

More examples.
https://powerpivotpro.com/2014/11/flat-to-star-transformation-using-dax-query/
https://medium.com/@Konstantinos_Io...sion-tables-from-multiple-tables-b7126644cc0b