DAX Help - Define Variable Using Two Tables

[Anonymous]

Community,

I currently have three tables. I would like to use two of the tables to define a variable, then apply the variable along with values in the third table to calculate a value.

Below are the tables and what I would like to achieve, but getting the DAX correct has been a bit of a struggle, any help would be much appreciated. (Also in the example included, I would not have the option to merge any of the tables)

Create a Measure, CL_ATD1

• Where CL_ATD1 = ((Slope[CL_x^2]*V)-(Slope[CL_x]*V))+(Slope[CL_icpt])
• What is V?
  • V is the value where PassID[Pass ID] and ATD[Pass ID] match, then the column in PassID that has the value 100, retrieves the matching columns value from the ATD table where ATD[Attitude ID] = “1”
  • In this example V = 1.5
• In this example the measure CL_ATD1 =
  • ((0.00232795*1.5^2)-(0.000113306*1.5))+(-0.846558)
  • = -0.84149

[v-gizhi-msft · Accepted solution]

Hi,

Please unpivot table ATD and table PassID first:

Table ATD:                                                         Table PassID:

   

Then try this measure:

Measure = 
var SameID = CALCULATE(MAX(ATD[Pass ID]),FILTER(ATD,ATD[Attitude ID]=ATD[Pass ID]))
var Attribute = CALCULATE(MAX(PassID[Attribute]),FILTER(PassID,PassID[Pass ID]=SameID&&PassID[Value]=100))
var variable = CALCULATE(MAX(ATD[Value]),FILTER(ATD,ATD[Attitude ID]=ATD[Pass ID]&&ATD[Attribute]=Attribute))
return
MAX(Slope[CL_x^2])*variable*variable-MAX(Slope[CL_x])*variable+MAX(Slope[CL_icpt])

Choose a card visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[Anonymous]

@v-gizhi-msft ,

Do you have any thoughts on how to modify the DAX to solve for each of the different "Attitude ID"? The current DAX only solves for one of the "Attitude ID".

I would like to end up with a table that looks something like this (CL_ATD1 is your measure in the original post), but with the correct calculated values. (In this example the variables need to muptiple with would be 1.5, 0, -1.5, -2.5, -3.5)

I have tried introducing some HASONEVALUE in to the DAX but no luck in getting the correct values yet. Any help with this would be much appreciated. Also if you like I can make this a new post.

Thanks!

[Anonymous]

@v-gizhi-msft ,

Thanks so much for the help!!

Your DAX works perfect.