Help required on DAX measure

[Dave1972]

Hi All,

I have a question that I need help on...See situation below.

I would like to know for each of my trips what the load capacity is. I need to get this from another table but am not able to resolve. I need to be able to see per [tripnr] what the load capacity is of the vehicle. Vehicle and Rcombi are currently actively connected through [truck] , but the capacity comes from the trailers which is an inactive relation with [vehiclenr]

Table Vehicle 

[vehicle nr]	active 1 - * relation to RComb [truck], inactive 1 - * relation to RComb [trailer]
[Loading capacity]	holds value for load capacity of the vehicle nr

Table RComb

[truck]	active * - 1 relation to  Vehicle [vehiclenr]
[trailer]	inactive * - 1 to  Vehicle [vehiclenr]
[trip]	active * - 1 to Trip [tripnr]

Table Trip

[tripnr]

active 1 to * relation to  RComb [trip]

How do I get a measure that allows me to give the [loading capacity] per [tripnr].

Should I use Use Relationship formula..? Hope it is clear. Thanks for help! 

[Dave1972]

Hi NV Durga Prasad,

Thanks for your repoonse but I had tried that already and get incorrect outcome.

So what I did is that I created in Table vehicle a measure that is [# Loading capacity] = SUM('Vehicle'[LoadingCapacity]). In 1st table below you see that gives per vehiclenr the correct answer.

Hower I I then create a measure in table RComb that 

# Ldm Cap Trailer = CALCULATE([# Loading capacity], USERELATIONSHIP(Vehicle[Vehiclenr], RComb[Trailer])) I am getting the sum as output in that table or in the Trip table...

So I need per trailer or trip the loading capacity of the vehiclenr, like value 8 or 7 or 13 as per first table...

I also tried with SUMX in measure but does not help either...

What am I missing here.

[nvprasad]

Hi Dave,

Yes when you are having inactive relationship then "USE RELATIONSHIP" function turns active during measure run time. 

typical syntax is like "CALCULATE ([MEASURE], USERLATIONSHIP(TABLE1[COLUMN],TABLE2[COLUMN]))

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad