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

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!