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-axis in regression equation. Basically I need relation between any of two column out of 400 based on slicer selection.Unpivot option has challenge for big data. Also aggregation issue during unpivot. Kindly help to fix it.Thanks in advance.

tamerj1 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!

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

Hi tamerj1,Great solution that you provided.Also I need some suggesions. I am explaining what I did.I used header as x-axis & y-axis. Then created measures using SWITCH DAX for column selection at both axis. Now, Simply I used craydec regression chart which is working fine, showing equation perfect but in pictorial view. So that finally we have to use LINEST/LINESTX dax.So, can we create LINEST/LINESTX DAX using measures, otherway what you did.

tamerj1 Very fair observations. I'm still digging into your code and seeing what it is doing.

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?

Dynamically select column based on slicer for regression/correlation calculation

15 Replies

tamerj1

Community Champion

2 years ago

Hi Anonymous

This is higly challenging. If you provide a sample PBIX file with sample data, I would accept the challenge (no guarantees on results)

Greg_Deckler do you think that would be another use case of TOCSV?

Anonymous

Not applicable

2 years ago

Hi tamerj1 ,
I am not able to insert pbix file. You can create any of 3-4 column (imagin we have 400+ column) like this. I need to select any of two column for regression equation as x & y-axis. I used LINEST Dax for calculating regression equation.

tamerj1

Community Champion

2 years ago

Hi Anonymous
Here I'm using Field Parameters for dynamic selection of the X-Axis and Y-Axis. However, when trying to calculate the slope and intersection dynamically I hit the wall :-DI have a strong feeling that there should be a much simpler way to accomplish the same but this is what I was able to accomplish so far.
Worth mentioning that with hundreds of columns and thousands of rows this solution might not be feasible in terms of performance.
Also note that I don't have very strong visualization skills and I didn't know how how to add the manual trend line to the scattered plot so I just overlapped it with a line chart. Please do not laugh at that 🙂

Greg_Deckler Appreciate your input on this. Was that over thinking or it is just what it is?

Regression Value = 
VAR XColumn = MAX ( 'X-Axis'[Parameter] )
VAR YColumn = MAX ( 'Y_Axis'[Parameter] )
VAR T = ALLSELECTED ( 'Table' )
VAR NumberOfRows = COUNTROWS ( T )
VAR String1 = TOCSV ( T, -1, ",", TRUE )
VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
VAR T1 = GENERATESERIES ( 1, NumberOfRows, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, [Value] + 1 ) )
VAR T3 =
    GENERATE ( 
        T2, 
        VAR HeaderString = [@Headers]
        VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
        VAR DetailString = [@Details]
        VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
        VAR Length = PATHLENGTH ( HeaderItems )
        VAR T4 = GENERATESERIES ( 1, Length, 1 )
        VAR T5 = SELECTCOLUMNS ( T4, "@Header1", PATHITEM ( HeaderItems, [Value] ), "@Detail1", PATHITEM ( DetailItems, [Value] ) )
        VAR T6 = SELECTCOLUMNS ( T4, "@Header2", PATHITEM ( HeaderItems, [Value] ), "@Detail2", PATHITEM ( DetailItems, [Value] ) )
        VAR T7 = FILTER ( T5, [@Header1] = "'Table'[" & XColumn & "]"  )
        VAR T8 = FILTER ( T6, [@Header2] = "'Table'[" & YColumn & "]"  )
        RETURN
            CROSSJOIN ( T7, T8 )
    )
VAR LinestTable = LINESTX ( T3, [@Detail2], [@Detail1] )
VAR Slope = MAXX ( LinestTable, [Slope1] )
VAR Intercept = MAXX ( LinestTable, [Intercept] )
VAR X = 
    MAXX (
        'Table',
        VAR T1 = CALCULATETABLE ( 'Table' )
        VAR String1 = TOCSV ( T1, 1, ",", TRUE )
        VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
        VAR T2 = SELECTCOLUMNS ( { ( 1, 2 ) }, "@Headers", PATHITEM ( Items1, 1 ), "@Details", PATHITEM ( Items1, 2 ) )
        VAR T3 =
            GENERATE ( 
                T2, 
                VAR HeaderString = [@Headers]
                VAR HeaderItems = SUBSTITUTE ( HeaderString, ",", "|" )
                VAR DetailString = [@Details]
                VAR DetailItems = SUBSTITUTE ( DetailString, ",", "|" )
                VAR Length = PATHLENGTH ( HeaderItems )
                VAR T4 = GENERATESERIES ( 1, Length, 1 )
                RETURN
                    FILTER ( 
                        SELECTCOLUMNS ( 
                            T4, 
                            "@Header", PATHITEM ( HeaderItems, [Value] ),
                            "@Detail", PATHITEM ( DetailItems, [Value] ) 
                        ),
                        [@Header] = "'Table'[" & XColumn & "]" 
                    )
            )
        RETURN
            MAXX ( T3, VALUE ( [@Detail] ) )
    )
RETURN
    Slope * X + Intercept

Community 682 - TOCSV Horizontal Curve Regression.pbix69 KB

Forum Discussion

Dynamically select column based on slicer for regression/correlation calculation

15 Replies

Related content

Dynamic Rank Calculation

Dynamic Calculation Different Period

Dynamic Variable in Calculated Columns

Dynamic Calculation Group Item

Dynamic y-Axis calculation with Deneb