Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX Help - Define Variable Using Two Tables

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

 

 

 
 
 
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

3 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      v-gizhi-msft ,

      Thanks so much for the help!!

      Your DAX works perfect.

       
       
       
       
       
       
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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!