How not to impact a filter in a measure

[Anonymous]

Hi, i'd like a filter not impact in a measure, when selecting this. Actually, when i activate the filter, bar of Quantità No Prom disappear but i want that these stay there.

Without filter:

Selecting filter:

if it helps, here is the measure for Quantità No Prom

Quantità No Prom=

VAR _1 =IF([Quantità No Prom]>0, [Quantità No Prom]/[Giorni No Prom])

VAR _2 = CALCULATE(COUNTX(VALUES(ANCLI[PuntID]),_1),FILTER(VALUES(ANCLI[PuntID]),[Quant]<>BLANK()))

VAR _3 = DIVIDE(_1,_2)

RETURN

IF([Solo Quant],_3 ,BLANK())


i hope you can help me,
thank you

[shafiz_p]

Hi @Anonymous  You can use ALL() function to remove filter from specific table or column. If you don't want your calendar table interact with the measure then use ALL('Calendar').

Hope this helps!!
If this solved your problem, please accept it as a solution!!

Best Regards,
Shahariar Hafiz

[Anonymous]

Hi, thanks, but where i have to put ALL() in my DAX formula?

[shafiz_p]

Inside calculate function. 

Try this:
CALCULATE(COUNTX(VALUES(ANCLI[PuntID]),_1),FILTER(VALUES(ANCLI[PuntID]),[Quant]<>BLANK()), all(Calendar))

[Anonymous]

it doesn't work. How can i edit this?

[shafiz_p]

You have used IF condition, where you would not be able to use ALL() filter funtion. Try this for var _3 :

VAR _3 =

CALCULATE(

    DIVIDE(

        IF([Quantità No Prom]>0, [Quantità No Prom]/[Giorni No Prom]),

        CALCULATE(

            COUNTX(VALUES(ANCLI[PuntID]), IF([Quantità No Prom]>0, [Quantità No Prom]/[Giorni No Prom])),

            FILTER(VALUES(ANCLI[PuntID]),[Quant]<>BLANK())

        )

    ),

    ALL(Calendar)

)

[Anonymous]

i tried but doesn't work

[shafiz_p]

Since I don't know about your data, your motivation, it is hard to tell specific solution. Replace your measure with the below code (Try this, If not I am sorry):

Quantità No Prom=

CALCULATE(
    IF(
    [Solo Quant],

    DIVIDE(

        IF([Quantità No Prom]>0, [Quantità No Prom]/[Giorni No Prom]),

        CALCULATE(

            COUNTX(VALUES(ANCLI[PuntID]), IF([Quantità No Prom]>0, [Quantità No Prom]/[Giorni No Prom])),

            FILTER(VALUES(ANCLI[PuntID]),[Quant]<>BLANK())

        )

    ),
   BLANK()

 ),

 ALL(Calendar)

)

Replace calendar with your table. This measure will check if true, the evalute divide else blank and remove all filters from calendar table.

Hope this solve your problem.

[Anonymous]

i tried again, but it didn't work. 

[shafiz_p · Accepted solution]

I have tested such scenario with a dummy data. It is perfectly working.

This is my only data set. I have tried to find out average where that average should not affected by date filter.

Here is the dataset:

See my measure and and slicer:

See test is not affected when I select other data:

I would suggest you to independently check measure , is the responding with the slicer selection or not.