COUNTIF and COUNTA in Power BI

[jjasper]

Hello, 

I am trying to understand how to do the countif and counta functions in Power BI but I am just getting myself confused. I have two equations (well acutally 5 but they all minic each other) that I need to recreate in Power BI. 

Here is the excel version: 

COUNTIF(Table1[@[Proper hand hygiene performed before donning PPE]:[Gloves applied]],"Yes")

IF([@Observation]="Donning",[@[Donning Steps]]/5,"")

I have the exact same columns in Power BI. I did straight up import from the excel file to Power BI. I have now set up the connections for the data to go into SQL and then pull into Power BI hence why I am trying to recreate the equations. I need to know how many "Yes's" for each record for each type of PPE. Then I need to know the compliance for each type of PPE. 

For each step I need to know if they answered Yes or No and then will create a count to tell me how many of the steps they answered yes. Then from there I need to take the number of Yes steps and divide by the number of steps to get the compliance rate. 

I really hope this makes sense. 

TIA

[jjasper]

[AlB · Accepted solution]

@jjasper 

It would be best to change the structure of the table in PQ, unpivotting the columns so that you have the info on the compliance with each type of PPE in rows rather than in columns. Then it is much easier to do what you are aiming for. If you copy here a sample of your table (in text tabular-format, so that the contents can be copied), I can run some quick test and show you the M code and the DAX code to get there

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jjasper]

+------------------------+---------+------------------------+----------------------------------------------------+-------------+-------------------------------------------+------------------------------------+------------------------------------+--------------------------+---------------+----------------------------------------------------+------------------------------------------------+-----------------------------------------------------------------+------------------------------------------------------------------------------+-----------------------------------------------+----------------------------------------------------+-----------------------------+---------------------------------------------+----------------------------------------------------------------------+----------------------+------------------------------------------------------------------------+-----------------------+-------------------------+-----------------------+----------------------------------------------------+---------+--------+--------------+-------------------+--------------+-------------------+-------------------------------------+-----------+-------+
| Date | Village | LocationAuditCompleted | Rolseofpersonbeingobserved | Observation | ProperhandhygienepeformedbeforedonningPPE | Gownputonwithlimitedtouchingofgown | Maskappliedandorpositionedproperly | Eyefaceprotectionapplied | Glovesapplied | CommentsDonning | Appropriatesignageispostedoutsideresidentsroom | Thereisadequatesuppliesavailableintheisolationcartbindoorhanger | Amemberoftheleadershipteamordeligatechecksuppliesdailyandbinsarestockeddaily | Suppliesareaccessibletoteammemberwhenrequired | Commentssuppliesaccesspreparedness | Willdoffingbeobservedaswell | Agarbagebinisavailableattheresidentsdoorway | Glovesandgownareremovedinamannertopreventcontaminatingclothingorskin | Handhygieneperformed | Eyefaceprotectionremovedandcleanedbeforereturningtoinfectioncontrolbin | Handhygieneperformed2 | Maskischangedasrequired | Handhygieneperformed3 | Commentsdoffingprocedure | Setting | Region | DonningSteps | DonningCompliance | DoffingSteps | DoffingCompliance | SuppliesAccessPreparednessCompliace | Attribute | Value |
+------------------------+---------+------------------------+----------------------------------------------------+-------------+-------------------------------------------+------------------------------------+------------------------------------+--------------------------+---------------+----------------------------------------------------+------------------------------------------------+-----------------------------------------------------------------+------------------------------------------------------------------------------+-----------------------------------------------+----------------------------------------------------+-----------------------------+---------------------------------------------+----------------------------------------------------------------------+----------------------+------------------------------------------------------------------------+-----------------------+-------------------------+-----------------------+----------------------------------------------------+---------+--------+--------------+-------------------+--------------+-------------------+-------------------------------------+-----------+-------+
| 2020-10-09 1:20:24 PM | TW | Williamsburg West | PSW/ PCA | Donning | Yes | Yes | Yes | Yes | Yes | null | Yes | Yes | Yes | Yes | null | No | null | null | null | null | null | null | null | null | RH | East | null | null | null | null | null | ID | 1 |
+------------------------+---------+------------------------+----------------------------------------------------+-------------+-------------------------------------------+------------------------------------+------------------------------------+--------------------------+---------------+----------------------------------------------------+------------------------------------------------+-----------------------------------------------------------------+------------------------------------------------------------------------------+-----------------------------------------------+----------------------------------------------------+-----------------------------+---------------------------------------------+----------------------------------------------------------------------+----------------------+------------------------------------------------------------------------+-----------------------+-------------------------+-----------------------+----------------------------------------------------+---------+--------+--------------+-------------------+--------------+-------------------+-------------------------------------+-----------+-------+
| 2020-10-12 9:55:46 AM | TW | Emma's East | PSW/ PCA | Donning | Yes | Yes | Yes | Yes | Yes | null | Yes | Yes | Yes | Yes | null | No | null | null | null | null | null | null | null | null | RH | East | null | null | null | null | null | ID | 2 |
+------------------------+---------+------------------------+----------------------------------------------------+-------------+-------------------------------------------+------------------------------------+------------------------------------

[AlB]

Hi @jjasper 

Can you show a sample of your table, where all columns are visible?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers