Forum Discussion
Dynamically select column based on slicer for regression/correlation calculation
Community Championtamerj1 I'll have to take a look at this in greater detail. Been a while since I played around with regression analysis and I haven't really looked much at LINEST function. Thanks for the PBIX. I'll have a look at what you did here, it looks pretty interesting.
I do come back to that even with 400 column and let's say 10,000 rows, that's only 4 million rows unpivoted so shouldn't be that problematic and shouldn't increase the size of the dataset that tremendously since there should be pretty decent compression. Would likely simplify this problem greatly. Always up for a challenge though!
Community ChampionGreg_Deckler
You are absolutely right and I am always with simplest possible solution. However, I have noticed that in many cases people where only allowed to create live connections to pre-published datasets that they don't have the privilege to edit and thus trying very hard to deal with their unpivotted data to perform some sort of aggregation across columns.
One more thing I would like to add is that unpivoting 400 columns using power query is not that efficient. The last time I gave that advise to person in the community he ended up with total crash of his pbix file.
- Greg_Deckler
tamerj1 Very fair observations. I'm still digging into your code and seeing what it is doing.
- Greg_Deckler
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
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
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*!