Two different behaviour with similar measure

[Matthieu_R]

Hello,

I'm trying to understand why sometimes i need to user FILTER in my CALCULATE measure. Let me explain the context :

I have this measure :

CA HT pec locations à J = 
CALCULATE(
    SUM(fait_pec_ligne[pec_ligne_ht]),
    fait_pec_ligne[pec_ligne_type] = 1
    ,(fait_pec_file_active[pec_etat_general] = "En Traitement" || fait_pec_file_active[pec_etat_general] = "Bloquée")
)

This works as expected. It calculates a SUM from the table "fait_pec_ligne" and filter with data from another table "fait_pec_file_active".
And i don't need to use FILTER to get the expected result. Those two fact tables are connected with active relationships (through a dimension table between them).

Now i have another measure :

CA HT N-2 à N = 
 CALCULATE(
    SUM(fait_facture[facture_ht])
    ,dim_calendrier[calendrier_annee_numero] >= YEAR(TODAY()) - 2
    && dim_calendrier[calendrier_annee_numero] <= YEAR(TODAY())
    )
 )

It does pretty much the same thing (a SUM) on a fact table "fait_facture". And it filters the rows that we try to sum, with the dimension calendar table.
If i use this measure in a line chart (Y axis) and i put the year from the calendar table (X axis), the line are not filter to the period asked in the measure. My lines starts before YEAR(TODAY()) - 2.
The two tables are connected together with an active relationship with a date column.
And now if i modify my last measure this way :

CA HT N-2 à N = 
 CALCULATE(
    SUM(fait_facture[facture_ht])
    ,FILTER(
        dim_calendrier
        ,dim_calendrier[calendrier_annee_numero] >= YEAR(TODAY()) - 2
        && dim_calendrier[calendrier_annee_numero] <= YEAR(TODAY())
    )
 )

It is working well and my line chart is filtered to the year i asked for.

And i don't get the point of why in the first measure, i don't need to use FILTER to filter a table with another connected table and in the second measure, i need to use FILTER in order to get a correct result in my line chart.

In both case, the tables are connected with active relationships.

Thnks in advance for your help

[speedramps]

The best way to learn about CALCULATE and FILTER is to play and experiment ...

For example use this test data

EmployeeGradeSalary

Employee	Grade	Salary
Peter	1	1000
Mary	2	2220
Sue	3	3450
Jane	4	1560
Henry	1	4890
Gill	2	5360
Oscar	3	7545
Sheila	4	8000
Paul	5	7800

and use this example output

Look at my exampel PBIX

The FILTER command creates a temporay table.
For example

Staff on 2 to 4 = 
var tempfile  = 
FILTER(
    yourdata,
    yourdata[Grade] >= 2 &&
    yourdata[Grade] <= 4)
RETURN
COUNTROWS(tempfile)

The SUM command default to the natural context

Salaries = 
SUM(yourdata[Salary])

The CALCULATE command can overide the natural context with the FILTER tempfile.
This can be done with or without VAR.
I prefer with a VAR because it is easier to learn, document,test, and debug.

Filter1 = 
var tempfile  = 
FILTER(
    yourdata,
    yourdata[Grade] >= 2 &&
    yourdata[Grade] <= 4)
RETURN
CALCULATE(
    SUM(yourdata[Salary]),
    tempfile
)

Filter2 = 
CALCULATE(
    SUM(yourdata[Salary]),
    FILTER(
    yourdata,
    yourdata[Grade] >= 2 &&
    yourdata[Grade] <= 4))

However, we can code the CALCULATE without the FILTER and get the same results
because DAX automically applies the FILTER to the CALCULATE in this scenario

Calculate 1 = 

CALCULATE(
  SUM(yourdata[Salary]),
   yourdata[Grade] IN {2,3,4}
   )

Calculate 2 = 

CALCULATE(
  SUM(yourdata[Salary]),
   yourdata[Grade] >= 2 &&
   yourdata[Grade] <= 4 
   )

Learn more about CALCUALTE and FILTER here

https://learn.microsoft.com/en-us/dax/calculate-function-dax

https://learn.microsoft.com/en-us/dax/filter-functions-dax#

https://www.youtube.com/watch?v=SOTQ3MiTXT4

https://www.youtube.com/watch?v=-oDpOfhgmzA

Please clcik the thumbs up for all this useful infomation

and click accept solution if you are now wisee about CALCULATE and FILTER,

Thank you

[johnt75]

There's 2 things here. In your second measure you are passing the entrire calendar table as a filter argument, you don't need to do that, you just need the filter conditions.

Also, because you are filtering on the year column as well as using it in the axis of the chart you need to use KEEPFILTERS so that the filter from the chart is merged with the filter in the measure, rather than being overwritten by it

CA HT N-2 à N =
CALCULATE (
    SUM ( fait_facture[facture_ht] ),
    KEEPFILTERS (
        dim_calendrier[calendrier_annee_numero]
            >= YEAR ( TODAY () ) - 2
            && dim_calendrier[calendrier_annee_numero] <= YEAR ( TODAY () )
    )
)

[Matthieu_R]

Sorry i did a mistake in the copy paste of my second measure. I've corrected it in my post.

[johnt75]

That's OK, but my point about KEEPFILTERS still holds. I'm guessing that when using the original measure you were not using the pec_etat_general column in the visual. There is a fundamental difference between applying filters on columns which are used in a visual or not. If the column is used in a visual then you need to use KEEPFILTERS, otherwise the filter from the visual will be overwritten.

[Matthieu_R]

You're right i was not using the pec_etat_general column in the visual, only in the measure.
Ok i will make some tests and try to use the pec_etat_general column in the visual to see the behaviour.
You say to use keepfilters, but it seems to work as expected with FILTER as well. Maybe the result i get with FILTER in the visual is wrong ? And should be more accurate with KEEPFILTERS ?

[johnt75]

You don't need to explicitly specify FILTER, that is handled automatically for you. Your first measure is actually translated by the DAX engine into

CA HT pec locations à J =
CALCULATE (
    SUM ( fait_pec_ligne[pec_ligne_ht] ),
    fait_pec_ligne[pec_ligne_type] = 1,
    FILTER (
        ALL ( fait_pec_file_active[pec_etat_general] ),
        fait_pec_file_active[pec_etat_general] = "En Traitement"
            || fait_pec_file_active[pec_etat_general] = "Bloquée"
    )
)