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 

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

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
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 

    Thank you very much for your help with this.

    Just tested and is exactly what i needed

    :smileyvery-happy:

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

    Hi gazzo1967 

    No problem, Happy to help!

     

    Best Regards,
    Mariusz

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

    Please feel free to connect with me.
    Mariusz Repczynski