Forum Discussion
Advocate IRecreate 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
- ibarrau
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. 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:
Regards,
Xiaoxin Sheng
maxanatskoHere is an original data - doc
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
