Help to properly calculate the Project Completion %

Power BI
4

Totally agree with Brian. In the same article you will find my comment too and you will see how subtle DAX is, the same function performs 2 different things at 2 different places, on top of it CALCULATE does its own N things, then their is nesting of functions or evaluation context. That’s why DAX appears to be difficult.

Look at the below image and if I ask you how this measure works, then you would probably say: first, ALL in the inner CALCULATE removes filter from product color column and then the outer CACLUATE returns the value of product Red, right?

NO! that’s not how it works :frowning: . First it creates a filter context containing Red color and then inner CALCULATE removes all the filters from the product color column and then at the end CALCULATE evaluates Total Sales in a filter context where colors are not filtered.

image
image422×794 27.5 KB

Now if I merge 2 CALCULATE I get the correct result, why? even though ALL is still in the code? Here is why: CALCULATE Mantra

First Products[Color] = “Red” is evaluated in the initial filter context and the values are kept aside for a moment, then ALL removes all the filters from the Products[Color] column and at the end Products[Color] = “Red” is applied to the filter context, within which Total Sales is evaluated.

image
image389×796 23.6 KB

To make you more confused(not the intention but just to amaze you): When you write Products[Color] = “Red” internally engine expands this predicate/boolean condition to

=
FILTER (
    ALL ( Products[Color] ),
    Products[Color] = "Red"
)

so the expanded version of the code looks like this:

Measure 5 =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Products[Color] ),
        Products[Color] = "Red"
    ),
    ALL ( Products[Color] )
)

The end result is still the same but as I said in the beginning ALL is performing 2 different things in 2 different places, inside FILTER it returns all unique Products[Colors], outside FILTER it is removing filter from Products[Color]

image
image441×803 27.8 KB

These small things and many other concepts that are not evident at first makes it so confusing but once you understand them you will appreciate how awesome DAX is.

I would suggest you start honing the below topics:

CALCULATE modifiers
Context Transition
Row Context & Filter Context
Nested CALCULATE

Another suggestion: start solving other people’s queries, then only you will get a good grasp of DAX. What works for me is when I provide a solution and that is not the desired one and someone comes in with a perfect solution that is point when I learn more.

Here is post related to context transition that I (BIFanatic) solved on Reddit, it will help you in understanding how context transition works in CALCULATE:

1 Like