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
rmeng
Helper II
Helper II

ALLEXCEPT function to preserve a filter on a given column

I need a formula that gives me the result like the pivot tables:

  • The dataset has several dimensions and if you look in the pictures Argentina could belong  to 2 different regions (many to many relationship).
  • So when I choose either Latam or BIR the result shoulb be the same 63.
  • I´ve created this # Attendees = CALCULATE(MAX(Attendees[#Attendees]);ALLEXCEPT(Attendees;Attendees[AttendeeIdentifier])),  but in the end the value is 80!

example 2.png

 

example 1.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

You need something like this:

[# Attendees] =
	SUMX(
		VALUES( Attendees[AttendeeIdentifier] ),
		CALCULATE(
			MAX( Attendees[#Attendees] )
		)
	)

even though... when I look at the first visual, it looks like you are also taking into consideration the region since the total is 126. The measure above will not give you 126, it'll return 63. However, if you change the measure so that it does return 126 for this visual... it'll not return 63 for the second visual. It'll return 126.

 

So, in a word, it looks like either your defnition is not consistent or... you want 1 measure to behave differently depending on whether there is a filter on region or not. However, please note that while the individual cells in a table can have a filter on region, the totals won't (for the majority of cases) and hance you won't be able to do what you want to do according to what your visuals show.

 

Best

D

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

You need something like this:

[# Attendees] =
	SUMX(
		VALUES( Attendees[AttendeeIdentifier] ),
		CALCULATE(
			MAX( Attendees[#Attendees] )
		)
	)

even though... when I look at the first visual, it looks like you are also taking into consideration the region since the total is 126. The measure above will not give you 126, it'll return 63. However, if you change the measure so that it does return 126 for this visual... it'll not return 63 for the second visual. It'll return 126.

 

So, in a word, it looks like either your defnition is not consistent or... you want 1 measure to behave differently depending on whether there is a filter on region or not. However, please note that while the individual cells in a table can have a filter on region, the totals won't (for the majority of cases) and hance you won't be able to do what you want to do according to what your visuals show.

 

Best

D

harshnathani
Community Champion
Community Champion

HI @rmeng ,

 

Instead of MAX, try using SUM.

 

This should give you the answer.

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

What I want is formula when I filter by Country that returns the max [#Attendee] of AAA ([attendeeidenfier]).

So in this case Argentina only have 1 Attendee instead of 2 for the first two rows.

Te total shoulb be When I filter by Argentina = 63 when I filter by LATAM = 63 and BIR = 63.

PS: I have multiple other dimensios in this table

example 3.png

 

Anonymous
Not applicable

To get the right number, you have to create the measure using SUMX and iterate through the identifiers in the current context getting the common value of the # Attendees field for the identifier. Then and only then will you get the number you want.

Best
D

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.

Top Solution Authors