cancel
Showing results for
Did you mean:
Helper II

## Dax formula

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]))`

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

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

2 REPLIES 2
Community Support

## Re: Dax formula

@BWL,

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

## Re: Dax formula

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

Regards,

BWL

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors