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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
AlejandroPCar
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

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

 

ALLSELECTED( 'Tabla Cruce')


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
parry2k
Super User
Super User

Try using ALLSELECTED(TableName)



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Hi @parry2k

That does not works. 

i just tested at your pbix and it worked

can you share what changed you made to your measure



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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? 

 

 

can you share your measure

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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 ) )

 

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. 

what is that 2nd measure you are lookign at does



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

not sure i followed you that you can fitler by nivel sector, you can filter by nivel sector, this change is only have impact on one measure "Numero Empresas Secor", so not sure what it will not filter. 

 

sorry man, because of this langugage challenge, having bit hard time to understand the request.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@parry2k

 

Wow... maybe your solution could be better than I expected in a first time. Amm, sorry about the hard time but yes your are right. Thank you very very much. Hope also you cna help me in my other post of ranking. 

 

Again Thank you. 

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

 

ALLSELECTED( 'Tabla Cruce')


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.