Excel CountIF Text Function in Power BI

[dimsandfacts]

Hi 

What is the best way to transfer the logic from excel like below to Power BI either in Power Query or in DAX ? Many thanks 

=(COUNTIF(A2:D2,"X")+COUNTIF(A2:D2,"XY"))/(COUNTIF(A2:D2,"X")+(COUNTIF(A2:D2,"XY") + (COUNTIF(A2:D2,"XYZ")

[Anonymous · Accepted solution]

Hi @dimsandfacts ,

Check if this is your expected output:

Column = 
VAR _Count_X =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column1] = "X" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column2] = "X" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column3] = "X" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column4] = "X" )
VAR _Count_XY =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column1] = "XY" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column2] = "XY" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column3] = "XY" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column4] = "XY" )
VAR _Count_XYZ =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column1] = "XYZ" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column2] = "XYZ" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column3] = "XYZ" )
        + CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Column4] = "XYZ" )
RETURN
    DIVIDE ( _Count_X + _Count_XY, _Count_X + _Count_XY + _Count_XYZ )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Greg_Deckler]

@dimsandfacts See if this helps: (1) Excel to DAX Translation - Microsoft Fabric Community