Dax formula

[BWL]

Dear all,

I have some trouble with DAX measures and I don't know how to solve it. This is my data model:


I'm trying to make several measures, but I can't get them correct, because of the different dimension tables.
These are the measures:

• Sums cost per truck per period
• Counts number of routes per truck per period
• Cost per truck per period devided by number of routes per period
  
  
• Sums cost per trailer per period
• Counts number of routes per trailer per period
• Cost per trailer per period devided by number of routes per period
  
  

I tried to get it fixed with USERELATIONSHIP to define which relationship the measure should use. But this does not work.  

Measure = CALCULATE(SUM(tbl_Cost[Cost]); USERELATIONSHIP(tbl_Cost[Costkey];tbl_Trucks[Costkey]))

 
Can someone please help me?

To clarify I made a demo file which you can find here:

https://www.dropbox.com/s/bx9nwl246qjpg1p/180528%20Demo%20file.pbix?dl=0

Many thanks in advance.

[v-chuncz-msft]

@BWL,

You may try using LOOKUPVALUE Function to add calculated columns first.

[BWL]

Hi @v-chuncz-msft,

Thanks for your reply!

Do you mean:
So instead of making a measure, make calculated columns in tbl_route with LOOKUPVALUE?

Regards,

BWL