Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
hi @skaul
converting to DAX, it looks like:
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
Name | Alias | Period | Source Unit | Unit | Value | Form Type |
Burgandy Complex | Consumed units | 1/1/2020 | M | CO2 tonnes | 17818.62 | Actual |
Burgandy Complex | Consumed units | 1/2/2020 | M | CO2 tonnes | 10310.76 | Actual |
Burgandy Complex | Consumed units | 1/3/2020 | M | CO2 tonnes | 19668.96 | Actual |
Burgandy Complex | Consumed units | 1/4/2020 | M | CO2 tonnes | 18229.06 | Actual |
Burgandy Complex | Consumed units | 1/5/2020 | M | CO2 tonnes | 20039.96 | Actual |
Burgandy Complex | Consumed units | 1/6/2020 | M | CO2 tonnes | 18712.27 | Actual |
Burgandy Complex | Consumed units | 1/7/2020 | M | CO2 tonnes | 17036.43 | Actual |
Burgandy Complex | Consumed units | 1/8/2020 | M | CO2 tonnes | 18402.42 | Actual |
Burgandy Complex | Consumed units | 1/9/2020 | M | CO2 tonnes | 16682.36 | Actual |
Burgandy Complex | Consumed units | 1/10/2020 | M | CO2 tonnes | 16128.51 | Actual |
Burgandy Complex | Consumed units | 1/11/2020 | M | CO2 tonnes | 18836.17 | Actual |
Burgandy Complex | Consumed units | 1/12/2020 | M | CO2 tonnes | 11010.48 | Actual |
Burgandy Complex | Consumed units | 1/13/2020 | M | CO2 tonnes | 18082.79 | Actual |
Burgandy Complex | Consumed units | 1/14/2020 | M | CO2 tonnes | 16181.71 | Actual |
Burgandy Complex | Consumed units | 1/15/2020 | M | CO2 tonnes | 15752.86 | Actual |
Burgandy Complex | Consumed units | 1/16/2020 | M | CO2 tonnes | 17887.4 | Actual |
Burgandy Complex | Consumed units | 1/17/2020 | M | CO2 tonnes | 20906.05 | Actual |
Burgandy Complex | Consumed units | 1/18/2020 | M | CO2 tonnes | 18855.13 | Forecast |
Burgandy Complex | Consumed units | 1/19/2020 | M | CO2 tonnes | 18855.13 | Forecast |
Burgandy Complex | Consumed units | 1/20/2020 | M | CO2 tonnes | 18655.13 | Forecast |
Burgandy Complex | Consumed units | 1/21/2020 | M | CO2 tonnes | 18555.13 | Forecast |
Burgandy Complex | Consumed units | 1/22/2020 | M | CO2 tonnes | 18355.13 | Forecast |
hi @skaul
try this:
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?
Hi,
You can convert this into DAX by using CALCULATE + SUM.
Example:
Here we are calculating SUM IF cakes = T1 or T2
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/
Proud to be a Super User!
I just need explanation for the excel expression?
What does excel expression represents?
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |