Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Is there a way that I can:
Have 5 sets of 2 columns. (Separate tables or not, doesn't matter)
Calculate the R value for each pair of columns.
Automatically find which is the highest R value and use the related data (the two columns used to calculate the highest Rvalue) in another calculation? Perhaps save it to a measure or something?
For example, with reference to the dummy table below:
Columns 1 & A correlation is calculated to be R1, correlation for columns 2 & A is R2 etc.
Auto find the highest R value out of the 5 calculated R values (R1, R2, R3, R4 or R5).
Use only the respective numbered column (1,2,3,4 or 5) and column A in other measures or calculations etc.
Such as plot them to a graph, find max value or divide one by the other, this last part doesn't matter and will change with my needs, I do not require assistance with this part. If there is a measure or dax code that can do what I need just leave this open ended or put any calculation in there please, such as divide column 1 (or 2,3,4,5 if they have the highest correlation with column A) by column A.
Thank you in advance.
R1 | R2 | R3 | R4 | R5 | ||||||
1 | A | 2 | A | 3 | A | 4 | A | 5 | A | |
Jan-20 | 44 | 13 | 27 | 13 | 17 | 13 | 35 | 13 | 11 | 13 |
Feb-20 | 21 | 14 | 50 | 14 | 16 | 14 | 46 | 14 | 20 | 14 |
Mar-20 | 8 | 15 | 48 | 15 | 38 | 15 | 28 | 15 | 38 | 15 |
Apr-20 | 0 | 16 | 24 | 16 | 20 | 16 | 42 | 16 | 5 | 16 |
May-20 | 22 | 17 | 31 | 17 | 37 | 17 | 22 | 17 | 33 | 17 |
Jun-20 | 31 | 18 | 26 | 18 | 32 | 18 | 50 | 18 | 13 | 18 |
Jul-20 | 4 | 19 | 39 | 19 | 20 | 19 | 31 | 19 | 44 | 19 |
Aug-20 | 26 | 20 | 14 | 20 | 6 | 20 | 45 | 20 | 26 | 20 |
Sep-20 | 20 | 21 | 37 | 21 | 41 | 21 | 5 | 21 | 28 | 21 |
Oct-20 | 2 | 22 | 38 | 22 | 49 | 22 | 48 | 22 | 7 | 22 |
Nov-20 | 12 | 23 | 4 | 23 | 46 | 23 | 15 | 23 | 37 | 23 |
Dec-20 | 49 | 24 | 26 | 24 | 26 | 24 | 9 | 24 | 17 | 24 |
The R values are not actually at the top of this data, they are just shown here to show their corresponding columns.
If you don't get answers for a long time... it means your question is not clear. Since you're a new member, please familiarize yourself with this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community
And then rephrase your question to be understandable. Thanks.
Thanks, I did read this beforehand, I have tried to rephrase, unfortunately I am struggling with the explanation and have no other data or formulas to provide.
I can calculate all of this in excel quite easily but need it to be dynamic in Power bi as the columns of data can change. Hopefully my rewording is better.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
86 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |