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
skaul
New Member

Conveting excel formula into DAX

Hi,
I have a following excel formula and I have to convert that into DAX query. need solution

=SUMIFS('Raw Data'!$F:$F,' Raw Data'!$A:$A,$A$4,'Raw Data'!$E:$E,$B6,' Raw Data'!$B:$B,$A6,' Raw Data'!$C:$C,">="&C$1,'Raw Data'!$C:$C,"<"&C$2)

6 REPLIES 6
FreemanZ
Super User
Super User

hi  @skaul

converting to DAX, it looks like:

Value =
CALCULATE(
    SUM(TableName[HeaderColumnF]),
    TableName[HeaderColumnA] = "Content in A4",
    TableName[HeaderColumnE] = "Content in B6",
    TableName[HeaderColumnB] = "Content in A6",
    TableName[HeaderColumnC] >= "Content in C1",
    TableName[HeaderColumnC] < "Content in C2"    
)

 

if there is any issue, please consider providing some sample data and @me.

 

Hi,

I am forwarding the sample data with expression for which I am required to convert excel expression to dax 

EXCEL EXPRESSION

=SUMIFS(' Raw Data'!$F:$F,' Raw Data'!$A:$A,$A$4,' Raw Data'!$E:$E,$B6,' Raw Data'!$B:$B,$A6,' Raw Data'!$C:$C,">="&C$1,' Raw Data'!$C:$C,"<"&C$2)

 

Sample data is present below

 NameAliasPeriodSource Unit UnitValueForm Type
Burgandy ComplexConsumed units1/1/2020MCO2 tonnes17818.62Actual
Burgandy ComplexConsumed units1/2/2020MCO2 tonnes10310.76Actual
Burgandy ComplexConsumed units1/3/2020MCO2 tonnes19668.96Actual
Burgandy ComplexConsumed units1/4/2020MCO2 tonnes18229.06Actual
Burgandy ComplexConsumed units1/5/2020MCO2 tonnes20039.96Actual
Burgandy ComplexConsumed units1/6/2020MCO2 tonnes18712.27Actual
Burgandy ComplexConsumed units1/7/2020MCO2 tonnes17036.43Actual
Burgandy ComplexConsumed units1/8/2020MCO2 tonnes18402.42Actual
Burgandy ComplexConsumed units1/9/2020MCO2 tonnes16682.36Actual
Burgandy ComplexConsumed units1/10/2020MCO2 tonnes16128.51Actual
Burgandy ComplexConsumed units1/11/2020MCO2 tonnes18836.17Actual
Burgandy ComplexConsumed units1/12/2020MCO2 tonnes11010.48Actual
Burgandy ComplexConsumed units1/13/2020MCO2 tonnes18082.79Actual
Burgandy ComplexConsumed units1/14/2020MCO2 tonnes16181.71Actual
Burgandy ComplexConsumed units1/15/2020MCO2 tonnes15752.86Actual
Burgandy ComplexConsumed units1/16/2020MCO2 tonnes17887.4Actual
Burgandy ComplexConsumed units1/17/2020MCO2 tonnes20906.05Actual
Burgandy ComplexConsumed units1/18/2020MCO2 tonnes18855.13Forecast
Burgandy ComplexConsumed units1/19/2020MCO2 tonnes18855.13Forecast
Burgandy ComplexConsumed units1/20/2020MCO2 tonnes18655.13Forecast
Burgandy ComplexConsumed units1/21/2020MCO2 tonnes18555.13Forecast
Burgandy ComplexConsumed units1/22/2020MCO2 tonnes18355.13Forecast

hi @skaul

 

try this: 

Value =
CALCULATE(
    SUM('Raw Data'[Value]),
    'Raw Data'[Name] = "Content in A4 of your Excel",    //replace the content inside ""
    'Raw Data'[Unit] = "Content in B6",
    'Raw Data'[Alias] = "Content in A6",
    'Raw Data'[Period] >= "Content in C1",
    'Raw Data'[Period] < "Content in C2"    
)

 

skaul
New Member

If i break the above expression 
=SUMIFS('Raw Data'!$F:$F,- means sheet name rawdata we have to check F column in excel

' Raw Data'!$A:$A,$A$4,- means we have to check from columA to column A to 4th row of column A
'Raw Data'!$E:$E,$B6,- means we have to check column E to B column 6th row

' Raw Data'!$B:$B,$A6,'-means column B to A6
' Raw Data'!$C:$C,">="&C$1,- means column C should be >=Column C first row ,

' Raw Data'!$C:$C,"<"&C$2)- means Column C should be < Column C 2nd row?

ValtteriN
Super User
Super User

Hi,

You can convert this into DAX by using CALCULATE + SUM. 

Example:

Here we are calculating SUM IF cakes = T1 or T2

ValtteriN_0-1672919253462.png

 

 
Cake Total = CALCULATE(SUM(Cakes[Sums]),'Cakes'[Cakes]="T1" || 'Cakes'[Cakes]="T2")

ValtteriN_1-1672919467973.png

 



End result is 500 as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I just need explanation for the excel expression?
What does excel expression represents?

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.

Top Solution Authors