Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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
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.
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |