Forum Discussion

gazzo1967's avatar
gazzo1967
Icon for Helper III rankHelper III
7 years ago
Solved

COUNTROW based on words

HI All I know this is newbie question (which i am) I would like to have a total count based on word/s in a row, this is so i can compare my sharepoint list data against a total count of a specific ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi gazzo1967 

    Providing that you have only two abriviations of Watch Commander ( A, B ), you could use the DAX expression below.

    WatchCount = 
CALCULATE( 
    COUNTROWS( 'User Information List' ),
    'User Information List'[JobTitle] IN { "Watch Commander A", "Watch Commander B" }
)

    This will give you the below result.

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi gazzo1967 


    The below measure will search for "Watch Commander" within a string.

    WatchCount = 
CALCULATE( 
    COUNTROWS( 'User Information List' ),
    IFERROR( SEARCH( "Watch Commander", 'User Information List'[JobTitle] ), 0 ) > 0
)
     
    Let me know if this is what you are looking for.
    if not, please can you prepare a sample that illustrates the data + outcome. 
     
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski



gazzo1967's avatar
gazzo1967
Icon for Helper III rankHelper III
7 years ago

Mariusz 

 

Hi

I have a Jobtitle column that has multiple Jobtitles (from the user infomation list on sharepoint)

Jobtiltes could be

Watch Commander A

Watch Commander B

Crew Commander

etc

 

I would like to get the total count of Watch Commanders from the column 

So i can use it as a comparison

 

Gary

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi gazzo1967 

Providing that you have only two abriviations of Watch Commander ( A, B ), you could use the DAX expression below.

WatchCount = 
CALCULATE( 
    COUNTROWS( 'User Information List' ),
    'User Information List'[JobTitle] IN { "Watch Commander A", "Watch Commander B" }
)

This will give you the below result.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

  • gazzo1967's avatar
    gazzo1967
    Icon for Helper III rankHelper III
    7 years ago

    Mariusz 

    Thank you works great but only need total count of A and B

     

    Gary

  • gazzo1967's avatar
    gazzo1967
    Icon for Helper III rankHelper III
    7 years ago

    Mariusz 

    Actually i just tried it and it doesn't work for me

     

    • Mariusz's avatar
      Mariusz
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hi gazzo1967 ,

      What is the issue?

       

      Best Regards,
      Mariusz

      Please feel free to connect with me.
      Mariusz Repczynski

       

      • gazzo1967's avatar
        gazzo1967
        Icon for Helper III rankHelper III
        7 years ago

        Mariusz 

        Thank you for responding again.

         

        I am creating a new measure and pasting the code but when i then 'drag'

        Jobtitl field - ID - WatcCount i have no figures

         

        What im after is 

        Count how many times Watch Commader appears in the column Jobtitle

        Jobtitle is populated with approx 600 different titles.

         

        Sorry if i didn't make it clearer :(

         

        Again i appreciate you time and help

        Gary