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 jobtitle.

I have tried

WatchCount = CALCULATE(COUNTROWS('User Information List'),'User Information List'[JobTitle]="Watch Commander")
And
WatchCount = CALCULATE(DISTINCTCOUNT('User Information List'[Id]),'User Information List'[JobTitle] = "Watch Commander")
 
But i can not see a figure for the count!
 
Am i doing this right?
 
As i said i am new to this and been a steep learning curve for Powerapps and Flow now the boss wants the outputs!!
Regards Gary
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



10 Replies

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

    Hi gazzo1967 

    Please can you provide a data sample and the outcome that is expected?



    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 

       

      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