Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

IDNombre de la variableOpinion
1Wine1
1Meat2
1Vegetables3
1Cereal4
1Candy5
1Chicken6
2Wine5
2Meat4
2Vegetables3
2Cereal2
2Candy1
2Chicken6
3Wine4
3Meat3
3Vegetables2
3Cereal1
3Candy6
3Chicken5
4Wine3
4Meat2
4Vegetables1
4Cereal6
4Candy5
4Chicken4
5Wine2
5Meat1
5Vegetables6
5Cereal5
5Candy4
5Chicken3
6Wine1
6Meat6
6Vegetables5
6Cereal4
6Candy3
6Chicken2
7Wine1
7Meat2
7Vegetables3
7Cereal4
7Candy5
7Chicken6

 

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Really thanks man..!! It was usefull... Proud to be a datanaut.!

Anonymous
Not applicable

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:
DIMENTIONCodeSubDimentionGeneralQ
FoodF1Image 
FoodF2Image 
FoodF3Image 
FoodF4Image 
FoodF5Image 
FoodF6Image 
FoodF0ImageGeneral1
GroceryG1Power 
GroceryG2Power 
GroceryG3Power 
GroceryG4Power 
GroceryG5Power 
GroceryG6Power 
GroceryG0PowerGeneral1
LexuryL1Cereal 
LexuryL2 Cereal 
LexuryL3Cereal 
LexuryL4Cereal 
LexuryL5Cereal 
LexuryL6Cereal 
LexuryL0CerealGeneral1

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.