Related Function and relationships

Hey,

How does a related function calculate in the below scenario when the cross filter is set to single direction.

The function is placed in the fact table and referencing the dimension table. The filter direction is from the dimension to the fact table. The rules state, a relationship must exist. The filter direction of the relationship is from the top going down yet the function is from the bottom of the stream going up.

Does it not matter which direction the filter is going. Also where video goes through related function in a measure.

For example in the sales table I place

				Total Sales = 
					SUMX ( 
						'Sales', 
							'Sales'[Quantity] * RELATED ('Product'[Unit Price] ) 
							)

Thanks in Advance.
Elizabeth Tachjian

Can you show the relationships window. The formula looks fine to me, is it not working? The dimension table Product holds unique records?

Paul

Enterprise%20DNA%20Expert%20-%20Small

Actually, this does matter. Related will only work on the many side of a 1:M relationship. If you try to use Related on a the many side of a 1:M it will not let you use that, it will only allow you to use RELATEDTABLE with some sort of aggregation)

So in your example, you are on the Many side (so RELATED is allowed) and will bring in the Unit Price for the current row that you are on. RELATED and RELATEDTABLE act just like CALCULATE and invoke context transition, which is why you will get the Product Unit price of that row, and not the entire total fo the Product[Unit Price] column.

Nick

Enterprise%20DNA%20Expert%20-%20Small

Hi Nick,

I dont understand the third sentence. In the first sentence you stated you can and third sentence you stated you can’t.

Actually, this does matter. Related will only work on the many side of a 1:M relationship. If you try to use Related on a the many side of a 1:M it will not let you use that, it will only allow you to use RELATEDTABLE with some sort of aggregation)

@ElizabethTachjian
Sorry about that, this should be clearer :slight_smile:

Nick

Enterprise%20DNA%20Expert%20-%20Small

Thank you Nick. With your help, I now have a clear understanding of the related and relatedtable functions. :slight_smile: