ALLEXCEPT function to preserve a filter on a given column

[rmeng]

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!

[Anonymous · Accepted solution]

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]

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]

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

[rmeng]

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

[Anonymous]

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