cancel
Showing results for
Did you mean:
Regular Visitor

## Recreate Excel formula with COUNTIF and MATCH

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.

4 REPLIES 4
Community Support

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.

DAX Function Reference

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

Here is an original data - doc

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Super User

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

Announcements