cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

External filters does not affect ALLEXCEPT

Hi, 

 

I have a measure with ALLEXCEPT that allows me count the number of companies in a sector.  In the first table there are 18 companies in that sector so the count works. But in the same sector, when I filter by city, the count still takes the whole sector. The result must be 2. How can I solve that?
Agrupacion.png

 

 

Hope you can help me. Here is my pbix file: https://1drv.ms/u/s!AtTnrgPUQzQCt342k1topoW817Qo

Have a nice day, 

 

Alejandro

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

you are giving column name in ALLSELECTED whereas it need to be only Table Name

 

ALLSELECTED( 'Tabla Cruce')





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

11 REPLIES 11
Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

Try using ALLSELECTED(TableName)






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper IV
Helper IV

Re: External filters does not affect ALLEXCEPT

Hi @parry2k

That does not works. 

Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

i just tested at your pbix and it worked

can you share what changed you made to your measure






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper IV
Helper IV

Re: External filters does not affect ALLEXCEPT

Hi @parry2k

 

Where exactly did you the change? I replace all the ALLEXCEPT in the masure with ALLSELECTED but the result is this: 
resultadofiltro.png

 But I need the total ( i.e. 18 ) for every row. Thats your result too? 

 

 

Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

can you share your measure

 

it has to be allselected(tablename), not any column in it.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper IV
Helper IV

Re: External filters does not affect ALLEXCEPT

Hi, @parry2k 

Here is it 

 

MEASURE TEST = 
	VAR ClaseS = CALCULATE( [Número de Empresas], ALLSELECTED( 'Tabla Cruce'[CIIUEmpresa] ) )
	VAR GrupoSu = CALCULATE( [Número de Empresas], ALLSELECTED( 'Tabla Cruce'[GRUPOEmpresa] ) )
	VAR DivisionS = CALCULATE( [Número de Empresas], ALLSELECTED( 'Tabla Cruce'[DIVISIONEmpresa] ) )
	VAR SeccionS = CALCULATE( [Número de Empresas], ALLSELECTED( 'Tabla Cruce'[SECCIONEmpresa] ) )
RETURN
	IF( [Número de Empresas] = 0, BLANK(), SWITCH( TRUE, 
					SUM( 'Tabla Sectores'[id_Sector] ) = 1, ClaseS,
					SUM ('Tabla Sectores'[id_Sector] ) = 2, GrupoSu, 
					SUM ('Tabla Sectores'[id_Sector] ) = 3, DivisionS,
					SUM ('Tabla Sectores'[id_Sector] ) = 4, SeccionS ) )

 

Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

you are giving column name in ALLSELECTED whereas it need to be only Table Name

 

ALLSELECTED( 'Tabla Cruce')





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper IV
Helper IV

Re: External filters does not affect ALLEXCEPT

Hi , @parry2k

 

Oh, you are right but... the problem and the important of the allexcept is when I filter with "Nivel Sector" Slicer. And thats my original measure does. Your solution works but cannot filter by "Nivel Sector" anymore. Thats I think is why allexcept is important. 

Highlighted
Super User IV
Super User IV

Re: External filters does not affect ALLEXCEPT

what is that 2nd measure you are lookign at does






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors