DAX calculated measure location

Question

I am a new DAX user and I am clearly mis-interpreting how the language handles relationship propagation for calculated measures and I am hoping someone can help me out with what I thought was a simple concept. Simple example:

3 Tables:
1) Concert_Event
2) Concert_Event_Details
3) City_Budget (Made to be completely unrelated for example purposes)

Concert Event has a (1:M) relationship to Concert_Event_Details and there is no relationship between City_Budget and tables 1,2.

Tables 1,2 link on Event_ID. Now let's say I want to sum the field "TicketSales" in Concert_Event_Details, BUT excluding eventID 2. My simple DAX mind would write a formula something like:

- TotTicketSales:= CALCULATE( SUM(TicketSales), FILTER(Concert_Event_Details, Concert_Event_Details[EventID] <> 2) )

Ok, next caveat is let's say I have about 50 measures to write, all of them always excludes eventID 2, SO I think I save time by simply filter out eventID 2 upon loading Concert_Event. Now here comes the question(s). I would expect if I wrote the calculated measure inside of Concert_Event_Details, I WOULD BE expected to add a FILTER line in DAX to says

FILTER(Concert_Event_Details, Concert_Event_Details[Event_ID] = RELATED(Concert_Event[Event_ID)

to MAKE SURE I am not including event_id 2 in my summation. I think this because I am writing this in the many side of the (1:M) relationship. From my testing, this is correct, I DO need that specific filter line to link back to the "1" side of the relationship.

FINALLY THOUGH, here is where I am not understanding the DAX engine behaviour. I would think that if I write this measure on the "1" side (Concert_Event), the relationship on Event_ID should propagate to the Many side and I SHOULD NOT need the additional FILTER code to specifically link on EventID. Through my testing I DO, and I don't understand why. Furthermore, if I write the same code in the completely unrelated table (City_Budget), I can arrive at the same ANSWER. There is no relationship between tables 3 and (1,2). What is going on and how am I completely missing the boat on relationships in the model and propagation?

Thanks for the responses in advance, not sure why this is tripping me up so much. I don't want to continually use unneeded linking code to explicitly call out relationships that I thought were already working in every calculated measure I write.

Cheeers!

Answer 1

The location of a calculated measure has no effect on the answer it provides. You can put calculations on any table you would like; putting the calc on the same table that contains the field you use it purely for organizational purposes.

Edit: And it seems you asked the same question on Technet and got an answer there as well.

Answer 2

The table a measure is housed in does not change how the DAX is evaluated, but it does change pivot table behavior as it relates to drill through.

A best practice is to put the measure in the table which is most directly related to the output of the measure to ensure the pivot table user will get expected results when drilling through the measure.