COUNTROW based on words

[gazzo1967]

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

[Mariusz · Accepted solution]

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]

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.

[gazzo1967]

@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 · Accepted solution]

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.

[gazzo1967]

@Mariusz 

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

[Mariusz]

Hi @gazzo1967 ,

What is the issue?

Best Regards,
Mariusz

Please feel free to connect with me.

[gazzo1967]

@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 · Accepted solution]

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.

[gazzo1967]

@Mariusz 

Thank you very much for your help with this.

Just tested and is exactly what i needed

[Mariusz]

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.

[gazzo1967]

@Mariusz 

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

Gary