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
tomknight
Helper I
Helper I

Averages across linked tables

Hi,

 

I have the data model below and am trying to get an average of Placements[Amount] for each combination of 'Location Ratings'[Domain] and 'Location Ratings'[Latest Rating]

 

If I put Domain and Latest Rating in a matrix and then choose Average of Amount for the value I get the same value for every field. I know this is to do with my data model and suspect it is because effectively I have a many-many relationship between Placements and Location Ratings.

 

I'm much more comfortable with SQL than DAX but if these were all inner joins it would work as an aggreagate query gy grouping on Domain and Latest Rating and averaging the Amount value.

 

So

 

1) Is my data model fundamentally broken and if so, how should I set it up so that I can get the answer I want.

 

or 2) What DAX can I use to get the average I want? 

 

I read this blog by Marco Russo and tried out some of the solutions but they didn't work. I'm not sure exactly why but maybe becuase there is only one link table involved in his example and I have two?

 

Thanks,

 

Tom

 

 

1 ACCEPTED SOLUTION
avanderschilden
Resolver I
Resolver I

Hi Tom,

 

Your model is completely fine.

 

DAX is not able to pass a filter from [Location Ratings] to [Locations] because of the filter direction. That is where the problem occurs. 

 

You need to activate bidirectional filtering between those two tables in your measure.

 

I think this will do the job;

 

Avg Amount =
CALCULATE(
AVERAGE(Placements[Amount]),
CROSSFILTER('Location Ratings'[Location ID],Locations[Location ID],Both)
)
 
Let me know!
 
Adrian

View solution in original post

4 REPLIES 4
suryaburaboyina
Frequent Visitor

Hi,

 

The data model you follwed is snowflake, as per my understanding the Locations and Location Ratings tables can be joied on the basis of location ID and then directly to the fact tables ( Hope placements tables is the Fact). 

 

when joining  two dimension tables together check which is unique key and do left join to the table which has uniqe IDs and thebn join the resultant table to fact.

 

Make sure that the model is star rather than snowflake, hope that will sufice the problem.

 

Thanks,

Surya 

avanderschilden
Resolver I
Resolver I

Hi Tom,

 

Your model is completely fine.

 

DAX is not able to pass a filter from [Location Ratings] to [Locations] because of the filter direction. That is where the problem occurs. 

 

You need to activate bidirectional filtering between those two tables in your measure.

 

I think this will do the job;

 

Avg Amount =
CALCULATE(
AVERAGE(Placements[Amount]),
CROSSFILTER('Location Ratings'[Location ID],Locations[Location ID],Both)
)
 
Let me know!
 
Adrian

Thank you so much Adrian - I tried everything to get that to work and it was pretty simple in the end!

 

I did turn bi-directional filters on the join between Location Ratings and Locations briefly to see if that would work but it didn't (although I may have done something differently there as well). I put it back because I have seen enough warnings about the potential impact of bi-directional filters in your model to put me off!

 

Is that DAX effectively turinng that relationship into a bi-directional filter but just for that measure only?

 

Thanks again for the help - it's much appreciated.

 

Tom 

 

 

Yes, it only activates bidirectional during the execution of the measure so you can keep it single in your model.

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