Forum Discussion

dimsandfacts's avatar
dimsandfacts
Regular Visitor
2 years ago
Solved

Excel CountIF Text Function in Power BI

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")

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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