Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey everyone,
I'm transitioning an Excel report into Power BI and want to adapt a COUNTIFS formula in the Excel report into a Power BI measure. The current COUNTIFS formula reads as follows: =COUNTIFS($K:$K,"Non-Compliant",$J:$J,$J2,$H:$H,$H2,$A:$A,$A2). K column is a Status column which has only two values Compliant or Non-Compliant. Column J is a Measure column containing text of different Measure descriptions, column H contains doctor names, and column A contains insurance names. The formula is obviously counting the occurrence of the Status Compliant or Non-Compliant in the given three way combo of Measure/Doctor/Insurance name, so one doctor that is paired with a given measure description and insurance that is Non-Compliant is counted occurrence of 11x. Any help is greatly appreciated.
Thanks
Solved! Go to Solution.
Substitute your own column/table names. This is a calculated column.
ColumnNonCompliant =
var _Payer = TableT[Column1]
var _Prov = TableT[Column2]
var _Meas = TableT[Column3]
var _stat = TableT[Status]
RETURN
COUNTROWS(FILTER(TableT, TableT[Column1] = _Payer && TableT[Column2] = _Prov && TableT[Column3] = _Meas && TableT[Status] = "Compliant"))
Post your data next time please, not a picture
No problem. Let me know how it works for you.
--
For posting on the forum:
Substitute your own column/table names. This is a calculated column.
ColumnNonCompliant =
var _Payer = TableT[Column1]
var _Prov = TableT[Column2]
var _Meas = TableT[Column3]
var _stat = TableT[Status]
RETURN
COUNTROWS(FILTER(TableT, TableT[Column1] = _Payer && TableT[Column2] = _Prov && TableT[Column3] = _Meas && TableT[Status] = "Compliant"))
Post your data next time please, not a picture
Worked just like I wanted it, thanks!
Thank you very much for your response. I tried to post the data sample as an attachment but could not find a button or option for file attachment.
Screen Shot of original Excel:
You will get a quicker answer if you post a small, fake data sample and show the desired result.
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |