Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamically select column based on slicer for regression/correlation calculation

Hi, I have approx 400 columns & thousand of rows. All column have numerical value.  My Requirement is that, Two slicers which select dynamically two different column that will represent as x & y-ax...
multiple column
Regression
Slicer
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

tamerj1 OK, first of all, this is very impressive code to do the column selection. Extremely creative. Where I *think* it could be improved potentially is in your calculation of X and Y. It occurs to me that after you calculate the slope and intercept that you could create a table of the original X and Y values doing something like this:

 

VAR __Table = SELECTCOLUMNS( T3, "Y", [@Detail2], "X", [@Detail1] )
 
Once you have that, you could use ADDCOLUMNS to calculate the estimated Y for each X using the slope and intercept and X value for each row. Then you could just grab the current X value in the visual and essentially "lookup" the corresponding calculated Y value (estimated) versus the actual measurement. What do you think?
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
2 years ago

Greg_Deckler 

That is a great observation! Wow! That would potentially improve the performance significantly and make code simpler and shorter. Let me try to implement it first thing in the morning. 
Thank you so much. Allways appreciate your input. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    tamerj1 Nevermind that last one, I forgot that you are using field parameters so all you will get back is the value of the parameter "Open", "Closed", etc. not the actual value for the x-axis. I think that might invalidate the previous thought I had as well. If you can't know the current value of the X-Axis then I don't think either approach will work.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    tamerj1 Now that I'm thinking about it, you could actually just grab the current value of X using MAX and calculate the estimated Y using the slope and intercept, no need for the intermediate table. I *think*!

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Greg_Deckler 
    I'm not sure I fully understand this point. Please clarify further.
    I wasn't able to try your previous suggestions today. I hope will be able to do it tomorrow. I'm so exited to finding a more simplified solution.