How do I convert Excel to DAX Formula?

Question

In my current project I am migrating Excel reports to Power BI, I need help converting the Excel formulas to DAX

Excel

COUNTIFS($K:$K,"*",$L:$L,E$31,$CS:$CS,1$D:$D,$E$15)

SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15)/1000

IFERROR((SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15,$P:$P," Key Launch Under Existing Hero")/1000)/E36,0)

COUNTIFS(`$BH$11:$BH$1001,"Commercial Innovation",$H$11:$H$1001,F$31,$C$11:$C$1001,$E$15,$BP$11:$BP$1001,"1")

SUMIFS($BB$11:$BB$1001$C$11:$C$1001,$E$15,$H$11:$H$1001,G$31,$BH$11:$BH$1001,"Commercial Innovation")/1000

COUNTIFS(OFFSET($L$10,0,0,10000,1),$D65,OFFSET($AL$10,0,INDEX($AL$6:$AU$6,MATCH($D$59,$AL$10:$AU$10,0)),10000,1),">0",OFFSET($CI$10,0,0,10000,1),"Yes",OFFSET($D$10,0,0,10000,1),$E$15)

IFERROR(AVERAGEIFS($CJ:$CJ, $K:$K, "*",$L:$L,$D113,$D:$D,$E$15), "-")

Answer 1

you can use Calculate function for any conditional calculation in DAX. There is no references and cells like excel in Power BI therefore there is no OFFSET or INDEX in Power BI but you can explain you problem in a table for find a solution instead of Offset and Index. For another formulas that you want:

Excel Countifs:

COUNTIFS($K:$K,"*",$L:$L,E$31,$CS:$CS,1$D:$D,$E$15)

DAX:

Measure Name = Calculate(count(K_Column), tbl_name[k_Column]="*",
tbl_name[L_Column]=E31_Value,...)

Excel Sumifs:

SUMIFS($AU:$AU,$L:$L,E$31,$CI:$CI, "Yes",$D:$D,$E$15)/1000

DAX:

Measure Name = Calculate(Sum(AU), tbl_name[L_Column]=E31_Value,
tbl_name[CI_Column]="Yes",...)

and you have iferror in DAX like excel:

iferror(calculate(sum(column_name), filter1, filter2))

There is a filter function in DAX for create a filtered table We don't have something like that in Excel.