Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Removing Filter with Allexcept

Hi, I have this problem:

 

I need to calculate an indicator for any product, the indicator should be calculated this way:

 

1) Sum of the cost.
2) That cost is divided by an area and the result it´s the indicator.

 

Notes:  For any product the cost should be the same because the cost it's the sum of everything and the area it's the total area so it's the same for any calculation.

 

I have used in the DAX formula the function ALLEXCEPT to remove the filters of the product but when I change of product in the slicer, it changes the value so it seems to me that the ALLEXCEPT it´s not working at all. Here it´s what happens.

 

When I have selected All the products and category products, that should be the value:

Filtro 1.PNGResultados.PNG

 

But when I select a product the numbers change and that should not happen, the result must be the same.

 

Filtro 2.PNGResultados 2.PNG

 

The DAX formula I have it´s:

 

 - Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALLEXCEPT(Dim_FlorAnColor,Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))

 

-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALLEXCEPT(Dim_FlorAnColor,Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))

 

Thanks.

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@Anonymous,

ALLEXCEPT() function will not ignore the slicers  you create, use ALL() function instead. Change your DAX to the following:

-Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@Anonymous,

ALLEXCEPT() function will not ignore the slicers  you create, use ALL() function instead. Change your DAX to the following:

-Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I have a similar request. I above I suspect might apply, however I can follow the logic due to it being to contextually to @Anonymous problem.

 

My problems is as follows:

 

How do I ensure a total figure is not affected by any filters selected by the user. I know I could use “Edit interactions” and switch of the filter interaction for the visual; however I would like to achieve this via DAX.
Background context:


I would like to ‘Distinct Count’ all items on a column. Each row represents a classroom workshop. Workshops are planned in advance on a yearly base. Occasionally a workshop needs to be cancelled due to below minimum confirmed attendees. To report all workshops scheduled Vs. those cancelled and those live I want an figure unaffected by any filters which I can use as a Denominator.

 

My inputs would be:

 

Table name: [Workshops Plan]

 

Total workshops scheduled or as a short hand to make it easier to read [TOTWrkshop. Sch]

 

[TOTWrkshop. Sch] = District Count of workshop IDs within column [IDs].
I would also like a total of cancelled workshops, which would be:

 

Total Cancelled Workshops i.e. [TOTWrkshop. Cnl]

 

[TOTWrkshop. Cnl] = District Count of workshop IDs within column [IDs] AND with a status of “Cancelled” within column [Workshop Status]

Anonymous
Not applicable

Works perfectly.

 

Thank you @v-yuezhe-msft Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.