Linear Regression DAX Help

[Anonymous]

Table 1

DIMENTION	Code	SubDimention	GeneralQ
Food	F1	Chicken
Food	F2	Chicken
Food	F3	Chicken
Food	F0	ChickenG	1
Grocery	G1	Sweet
Grocery	G2	Sweet
Grocery	G3	Sweet
Grocery	G0	SweetG	1
Lexury	L1	Cereal
Lexury	L2	Cereal
Lexury	L0	CerealG	1
Optional	O1	Meat
Optional	O2	Meat
Optional	O3	Meat
Optional	O0	MeatG	1

Table2

ID	Variable_code	Opinion
1	Wine	1
1	F1	2
1	F2	3
1	F3	4
1	F0	2
1	G1	3
1	G2	4
1	G3	5
1	G0	3
1	L1	4
1	L2	5
1	L0	4
1	O1	5
1	O2	6
1	O3	1
1	O0	3
2	Wine	1
2	F1	2
2	F2	3
2	F3	4
2	F0	2
2	G1	3
2	G2	4
2	G3	5
2	G0	3
2	L1	4
2	L2	5
2	L0	4
2	O1	5
2	O2	6
2	O3	1
2	O0	3
3	Wine	1
3	F1	2
3	F2	3
3	F3	4
3	F0	2
3	G1	3
3	G2	4
3	G3	5
3	G0	3
3	L1	4
3	L2	5
3	L0	4
3	O1	5
3	O2	6
3	O3	1
3	O0	3
4	Wine	1
4	F1	2
4	F2	3
4	F3	4
4	F0	2
4	G1	3
4	G2	4
4	G3	5
4	G0	3
4	L1	4
4	L2	5
4	L0	4
4	O1	5
4	O2	6
4	O3	1
4	O0	3
5	Wine	1
5	F1	2
5	F2	3
5	F3	4
5	F0	2
5	G1	3
5	G2	4
5	G3	5
5	G0	3
5	L1	4
5	L2	5
5	L0	4
5	O1	5
5	O2	6
5	O3	1
5	O0	3
6	Wine	1
6	F1	2
6	F2	3
6	F3	4
6	F0	2
6	G1	3
6	G2	4
6	G3	5
6	G0	3
6	L1	4
6	L2	5
6	L0	4
6	O1	5
6	O2	6
6	O3	1
6	O0	3
7	Wine	1
7	F1	2
7	F2	3
7	F3	4
7	F0	2
7	G1	3
7	G2	4
7	G3	5
7	G0	3
7	L1	4
7	L2	5
7	L0	4
7	O1	5
7	O2	6
7	O3	1
7	O0	3

Measure 12 =
Part of the furmula where I have to do SUMX(X*Y), and Greg_Deckler submitted this solution:

VAR _table =
    SUMMARIZE ( ALL ( 'Table12' ), [ID] )
VAR _table1 =
    ADDCOLUMNS (
        _table,
        "_y", SUMX (
            FILTER (
                ALL ( 'Table12' ),
                [ID] = EARLIER ( [ID] )
                    && [Variable_Code] = "Wine"
            ),
            [Opinion]
        ),
        "_x", SUMX (
            FILTER (
                ALL ( 'Table12' ),
                [ID] = EARLIER ( [ID] )
                    && [Variable_Code] = "F0"      //ChickenGeneral
            ),
            [Opinion]
        )
    )
VAR _table2 =
    ADDCOLUMNS ( _table1, "__product", [x] * [y] )
RETURN
    SUMX ( _table2, [product] )

The thing is, in ("[Variable_Code] = "F0"), that "F0" have to change with a filter with any general question (F0, G0, L0, O0).

How can I filtar that??

I have tryed with RELATED[Variable Code] = Table2[Code] and getting the filter in the sheet with "Image" column, like this: 

ChickenGeneral

SweetGeneral

CerealGeneral

MeatGeneral

 

I supposse to select one of this, and the option should change the result

[Anonymous]

Good day guys... I have this two tables: 

Table1

ID	Variable_code	Opinion
1	Wine	1
1	M1	2
1	V2	3
1	C1	4
1	G3	5
1	F0	6
2	Wine	5
2	M2	4
2	V0	3
2	C4	2
2	G0	1
2	F4	6
3	Wine	4
3	M1	3
3	V0	2
3	C3	1
3	G2	6
3	F6	5
4	Wine	3
4	M0	2
4	V0	1
4	C0	6
4	G2	5
4	F0	4
5	Wine	2
5	M1	1
5	V0	6
5	C0	5
5	G0	4
5	F0	3
6	Wine	1
6	M1	6
6	V0	5
6	C1	4
6	G0	3
6	F3	2
7	Wine	1
7	M1	2
7	V3	3
7	C0	4
7	G3	5
7	F0	6

Table 2

DIMENTION	Code	SubDimention	GeneralQ
Chicken	F1	Chicken1
Chicken	F2	Chicken2
Chicken	F3	Chicken3
Chicken	F4	Chicken4
Chicken	F5	Chicken5
Chicken	F6	Chicken6
Chicken	F0	ChickenGeneral	1
Grocery	G1	Grocery1
Grocery	G2	Grocery2
Grocery	G3	Grocery3
Grocery	G4	Grocery4
Grocery	G5	Grocery5
Grocery	G6	Grocery6
Grocery	G0	GroceryGeneral	1
Cereal	L1	Cereal1
Cereal	L2	Cereal2
Cereal	L3	Cereal3
Cereal	L4	Cereal4
Cereal	L5	Cereal5
Cereal	L6	Cereal6
Cereal	L0	CerealGeneral	1
Vegetables	B1	Vegetable1
Vegetables	B2	Vegetable2
Vegetables	B3	Vegetable3
Vegetables	B4	Vegetable4
Vegetables	B0	VegetableGeneral	1
Meat	O1	Meat1
Meat	O2	Meat2
Meat	O3	MeatGeneral	1

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.

Greg_Deckler submitted this solution:

Measure 12 =
VAR __table =
    SUMMARIZE ( ALL ( 'Table12' ), [ID] )
VAR __table1 =
    ADDCOLUMNS (
        __table,
        "__y", SUMX (
            FILTER (
                ALL ( 'Table12' ),
                [ID] = EARLIER ( [ID] )
                    && [Variable_Code] = "Wine"
            ),
            [Opinion]
        ),
        "__x", SUMX (
            FILTER (
                ALL ( 'Table12' ),
                [ID] = EARLIER ( [ID] )
                    && [Variable_Code] = "F0"      //ChickenGeneral
            ),
            [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.

Where "Chiken", "Meat", "Candy", etc, are my principal questions (General Questions).

So, in the part of the formula

[Variable_Code] = "F0", I need to change with a filter in the sheet, the other codes.

I have tryed with HASONVALUE, SELECTED VALUE, RELATED[Nombre de la variable] = TableN[Code], and nothing has happened....

[Anonymous]

Every product have a code, but is not filtering everyone in the results

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could try this way:

Step1:

Create the relationship between these two tables by 

Step2:

Use this formula to add a measure

Measure = 
VAR _table =
    SUMMARIZE ('Table12' , [ID] ,[Variable_code],[Opinion])
VAR _table1 =
    ADDCOLUMNS (
        _table,
        "y", SUMX (
            FILTER (
               ALL( 'Table12') ,
                [ID] = EARLIER ( [ID] )
                    && [Variable_Code] = "Wine"
            ),
            [Opinion]
        )
    )
VAR _table2 =
    ADDCOLUMNS ( _table1, "product", [Opinion] * [y] )
RETURN
    SUMX ( _table2, [product] )

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Image is a column where:

IF( GeneralQ = 1 , SubDimention , BLANK() ), so it has ChickenG, SweetG, CerealG, MeatG