I thought this would be a nice start of the monday morning...
Blogging about Microsoft Fabric, Power BI, Power Platform and other things I find interesting.
Monday, April 23, 2012
Friday, April 20, 2012
CALCULATE - The Queen of all DAX functions: From Row context to Filter context
This post is an update on AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE). With special thanks to Marco and Alberto explaining the topic of Evaluation Contexts so well! :)
A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.
Let's start with the 2 measures:
A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.
Let's start with the 2 measures:
(1)
AVERAGEX(
FILTER(
Organisatie,
Organisatie[Type] = "Woonlocatie"
),
SUM(Productie_OrganisatieGroei[AantalClienten])
)
AVERAGEX(
FILTER(
Organisatie,
Organisatie[Type] = "Woonlocatie"
),
SUM(Productie_OrganisatieGroei[AantalClienten])
)
(2)
SUMX(
Productie_OrganisatieGroei,
CALCULATE(
AVERAGE( Productie_OrganisatieGroei[AantalClienten]),
Organisatie[Type] = "Woonlocatie"
)
)
To understand the relationships between the tables, there is a 1-to-Many relation between Organisatie and Productie_OrganisatieGroei:
Datamodel |
Starting with measure 1, AVERAGEX is an iterator, so it creates a Row context on Productie_OrganisatieGroei. The FILTER on Organisatie however, because it is in a Row context, is not propagated to the many-side. So calculating the SUM of [AantalClienten] doesn't take the filter into account and takes the sum of the whole Organisatie table.
Measure 2 also starts with an iterator (SUMX) which creates a Row context, but then uses CALCULATE inside, which creates a Filter context on that one row. Remember that Filter context does propagate over relationships. Now that we have created a Filter context by using CALCULATE, the filter on Organisatie[Type] does propagate through the relationship and filters the Productie_OrganisatieGroei table. That is why this formula does produce a correct answer on the problem I had.
Once more the pivot table which has measure 1 and 2 in it:
Note: although the project was done with PowerPivot v1, I just imported the xlsx-file in SQL Server Data Tools (Visual Studio / former BIDS) to extract the datamodel for my workbook:
Wednesday, April 18, 2012
SSAS BISM Tabular Workshop
On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.
It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!
BISM Tabular Workshop |
PASS Community event |
I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.
Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!
Subscribe to:
Posts (Atom)
Featured Post
My DataGrillen Adventure: Speaking, Connecting, and New Friendships
I just got back from an incredible trip to DataGrillen, and I can’t wait to share my experiences with you. If you haven’t heard of it, Data...
-
Just before the holidays start, I'd like to share this little trick I came across in Power Query. I recently had the need to remove zero...
-
After Installing the PowerShell Power BI Management cmdlets a few months back, I finally managed to get a dataset refresh working, initiat...
-
I recently made the switch to Pulse (you can read about it here ), so I am working with lots of new clients lately. One of those clients wa...