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
BWL
Helper II
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:
PBI.PNG

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

    PBI1.PNG

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.

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@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.

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

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.