Recreate Excel formula with COUNTIF and MATCH

[maxanatsko]

Hi there.

Is it possible to reproduce this formula from Excel in DAX somehow?

=if(countif(offset(B12;0;2;1;IFERROR(MATCH("No";E12:G12;0);9999));"No") >= 2;"Accepted";if(countif(offset(B12;0;2;1;IFERROR(MATCH("No";E12:G12;0);9999));"Yes") > 0;"In Progress";if(A12=9999;"In Progress";"Fix Required")))

Source data looks like this:

Grade	State	Test Case ID	Test Case ID (2)	Test Name / Date	04.03.2017	28.02.2017
2	Fix Required	288145	898667	Test Name 2	No	Yes
9999	Accepted	277857	678656	Test Name 2	Yes	Yes

And in pBI its unpivoted and transformed to look like:

ID - Test Name - Date - State

*Notice that dates order is left to right. New ones are going to end up at the left side.

[Anonymous]

Hi @maxanatsko,

You formula seems not match to your data, can you share the available sample data?

In addition, power bi contains the if and iferror function, you can simply convert them, but I haven't found the function which function match the offset function. 

For countif and match, you can try to use countax and search function.

Reference link: 

DAX Function Reference

Regards,

Xiaoxin Sheng

[maxanatsko]

Here is an original data - doc

[Anonymous]

Hi @maxanatsko,

I can't find the function to replace the offset function, perhaps you can calculate in excel then get the result data to power bi.

Regards,

Xiaoxin Sheng

[ibarrau]

I'm no excel expert at all, but I pretty sure that countif, match, iferror and if exists in DAX with similar names. You should get to know the basics with powerbi check. 
It is so powerfull that i think you will success. Maybe the solution won't be the hole formula because you can relate tables and mix things in the visualizations, but would be possible.