Impact of FILTER and ALL function

[maciekimirekk]

Hi,

I have created two basics measures:

1. noFILTER = CALCULATE(

    count(Dane[order_id]),

    month(Dane[order_date]) = 9

    )

2. 

withFILTER = CALCULATE(

    count(Dane[order_id]),

    Filter(

        ALL(

            Dane[order_date]),

            MONTH(Dane[order_date] = 9)

    ))

 

I expect that the measure "nofilter" will change when I choose a different month, however, "withFilter" should remain the same as "FILTER" function ignores any other filters. However, it's not working in this way, so where I do the mistake in functions/my understanding of "FILTER" / "ALL" function. 

THank you in advance.

Br,

Maciej

[gadielsolis]

Hello @maciekimirekk .

I would like to propose the following solution:

I just created the following table to use it as example:

Then my solution would be the following:

1-Create a new table with the months (You can use Summarize to get this data). This table will not be conected to the original one, we will use this calendar in the slicer.

2-Create a slicer with the new calendar table

3-Create the following measures:

Sum of data =

        SUM('Table'[Data])

Lets say we want to keep Apr in the graph.

Filtered =

        CALCULATE([Sum of data],FILTER('Table','Table'[Month]="Apr"))

Measure to show the second Bar:

No Filter =

        IF(MAX('Table'[Month])=SELECTEDVALUE('Calendar'[Month]),CALCULATE([Sum of data],'Table'[Month]=SELECTEDVALUE('Calendar'[Month])))

Whit this you'll be able to change the dark blue bar by clicking on the slicer and the other one will show the same value.

Please let me know if this works for you.

[Fowmy]

@maciekimirekk 

These two measures are the same, when you include your filter within calculate as "MONTH(Dane[order_date] = 9)", it actual is converted to " FILTER ( ALL( Dane[order_date]),  MONTH(Dane[order_date] = 9) )" in the backend.

[maciekimirekk]

@Fowmy Thanks. So what's the point of using that if the calculation is done in the background?

[Fowmy]

@maciekimirekk 

I would like you to read the following article with reagrds to your question:

Simple Filters and Syntax Sugar in DAX functions ⚡ - Excelerator BI
Specifying multiple filter conditions in CALCULATE - SQLBI