Forum Discussion
DAX formula help
Hello... I have an issue.
I want to calculate a linear regression. The thing is that my data is in the same column, and I have not found the way to do that.
I have something like this:
| ID | Nombre de la variable | Opinion |
| 1 | Wine | 1 |
| 1 | Meat | 2 |
| 1 | Vegetables | 3 |
| 1 | Cereal | 4 |
| 1 | Candy | 5 |
| 1 | Chicken | 6 |
| 2 | Wine | 5 |
| 2 | Meat | 4 |
| 2 | Vegetables | 3 |
| 2 | Cereal | 2 |
| 2 | Candy | 1 |
| 2 | Chicken | 6 |
| 3 | Wine | 4 |
| 3 | Meat | 3 |
| 3 | Vegetables | 2 |
| 3 | Cereal | 1 |
| 3 | Candy | 6 |
| 3 | Chicken | 5 |
| 4 | Wine | 3 |
| 4 | Meat | 2 |
| 4 | Vegetables | 1 |
| 4 | Cereal | 6 |
| 4 | Candy | 5 |
| 4 | Chicken | 4 |
| 5 | Wine | 2 |
| 5 | Meat | 1 |
| 5 | Vegetables | 6 |
| 5 | Cereal | 5 |
| 5 | Candy | 4 |
| 5 | Chicken | 3 |
| 6 | Wine | 1 |
| 6 | Meat | 6 |
| 6 | Vegetables | 5 |
| 6 | Cereal | 4 |
| 6 | Candy | 3 |
| 6 | Chicken | 2 |
| 7 | Wine | 1 |
| 7 | Meat | 2 |
| 7 | Vegetables | 3 |
| 7 | Cereal | 4 |
| 7 | Candy | 5 |
| 7 | Chicken | 6 |
One part of the formula is sumx(XY), where "Y" is the opinion if "wine".
"X" have to variate among the opinion of "meat", "chicken", "candy", etc. So "X" have to be like a filter.
I really need help.
i.e.: Y= Wine ; X = Chicken
ID = 1: 1*6 = 6
ID = 2: 5*6 = 30
ID = 3 : 4*5 = 20
ID = 4: 3*4 = 12
ID = 5: 2*3 = 6
ID = 6: 1*2 = 2
ID = 7: 1*6 = 6
total = 82
I need that in a DAX formula
One way to do it:
Measure 12 = VAR __table = SUMMARIZE(ALL('Table12'),[ID]) VAR __table1 = ADDCOLUMNS(__table,"__y",SUMX(FILTER(ALL('Table12'),[ID]=EARLIER([ID]) && [Nombre de la variable]="Wine"),[Opinion]),"__x",SUMX(FILTER(ALL('Table12'),[ID] = EARLIER([ID]) && [Nombre de la variable]="Chicken"),[Opinion])) VAR __table2 = ADDCOLUMNS(__table1,"__product",[__x]*[__y]) RETURN SUMX(__table2,[__product])
3 Replies
One way to do it:
Measure 12 = VAR __table = SUMMARIZE(ALL('Table12'),[ID]) VAR __table1 = ADDCOLUMNS(__table,"__y",SUMX(FILTER(ALL('Table12'),[ID]=EARLIER([ID]) && [Nombre de la variable]="Wine"),[Opinion]),"__x",SUMX(FILTER(ALL('Table12'),[ID] = EARLIER([ID]) && [Nombre de la variable]="Chicken"),[Opinion])) VAR __table2 = ADDCOLUMNS(__table1,"__product",[__x]*[__y]) RETURN SUMX(__table2,[__product])You gave me this formula:
Measure 12 =
VAR __table =
SUMMARIZE ( ALL ( 'Table12' ), [ID] )
VAR __table1 =
ADDCOLUMNS (
__table,
"__y", SUMX (
FILTER (
ALL ( 'Table12' ),
[ID] = EARLIER ( [ID] )
&& [Nombre de la variable] = "Wine"
),
[Opinion]
),
"__x", SUMX (
FILTER (
ALL ( 'Table12' ),
[ID] = EARLIER ( [ID] )
&& [Nombre de la variable] = "Chicken"
),
[Opinion]
)
)
VAR __table2 =
ADDCOLUMNS ( __table1, "__product", [__x] * [__y] )
RETURN
SUMX ( __table2, [__product] )I do not have problems with "Wine" because that is the "Y" in linear regression. Instead, I need to filter the other part of the table: "Chicken", "Meat", "Candy", etc.I have another tableN with:DIMENTION Code SubDimention GeneralQ Food F1 Image Food F2 Image Food F3 Image Food F4 Image Food F5 Image Food F6 Image Food F0 ImageGeneral 1 Grocery G1 Power Grocery G2 Power Grocery G3 Power Grocery G4 Power Grocery G5 Power Grocery G6 Power Grocery G0 PowerGeneral 1 Lexury L1 Cereal Lexury L2 Cereal Lexury L3 Cereal Lexury L4 Cereal Lexury L5 Cereal Lexury L6 Cereal Lexury L0 CerealGeneral 1 Where "Chiken", "Meat", "Candy", etc, are my principal questions (General Questions).
So, in the part of the formula
[Nombre de la variable] = "Chicken", I need to change with a filter in the sheet, the option.
I have tryed with HASONVALUE, SELECTED VALUE, RELATED[Nombre de la variable] = TableN[Code], and nothing happened