Forum Discussion

Joak's avatar
Joak
Frequent Visitor
9 years ago

Excel in Dax

Hello everyone,    I'm trying to produce a new report using power BI and DAX language, however I can't find a way to reach what I want.    After reading some problems and their solutions, I haven...
Anonymous's avatar
Anonymous
Not applicable
9 years ago

The short answer is "You Don't"

 

The longer answer is to figure out what exactly you are doing in this calculation and figure out the best logic to do this with either DAX or Power Query.

 

For example, if your calculate is very much a "This row and the previous row" then you are going to have to do this within Power Query as part of your data import.  This requires you to create a sequential number row column and write a statement that makes use of the row number as part of the calculation.

 

If your logic is simply about adding up some values between rows that have some correlation, you can make use of a calculate statement to do this for you.  Something like:

Your Column = 
VAR YourCriteria = [CriteriaField]
RETURN

CALCULATE(
	SUM('YourTable'[Summing Field']),
	ALL('YourTable'),
	'YourTable'[CriteriaField2] = YourCriteria
)

This would give a row by row sum where the values in [CriteriaField2] match what is in the current rows [CriteriaField].

  • Joak's avatar
    Joak
    Frequent Visitor
    9 years ago

    Thanks for your answer. 

     

    I managed to find what I was searching for, using another formula.

     

    You're right, my calculate is very much a "this row and the previous row", however, each row has a specific criteria and regarding the amount of data I can't use such a formula. Also I wanted to have a sum of two average, here what I did:

     

    To succeed I created two new columns, excluding all the values except those which were matching my criteria (using = IF('Table'[xxx]="abcd";'Table'[yyy];0) ). In addition to that, I created a matching key (='Table'[zzz]&'Table'[www]&'Table[vvv])

    Then, I wrote a measure 

    Average of 'Table'[uuu] by MatchingKey =
    AVERAGEX(
    KEEPFILTERS(VALUES('Table'[MatchingKey]));
    CALCULATE(SUM('Table'[uuu]))
    )

     

    This made the sum of the average of values matching the same criterias. 

     

    I don't really know whether this is the simpliest solution but it works :)

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Joak,

       

      Could you please mark the proper answer as solution if it's convenient for you? That will be a help to the others.

      Best Regards!
      Dale