cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnmelbourne
Helper IV
Helper IV

Use countrows and seacrh or find ... or something different

Hi,

 

I am looking for different functions to do the following, perhaps using countrows and search or find but I am not getting the syntax correct.

 

In english, find where the


Total successful = CALCULATE([MeasureName], Category[Category] = "Successfully completed and stayed" || Category[Category] = "Successfully completed and left")

The above works fine, but I am looking to do a different way using new functions.

 

Any assistance appreciated.

 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @johnmelbourne ,

 

You may create a measure as below:

_Total successful = 
var _search1=SEARCH("Successfully completed and stayed",MAX('Category'[Category]),1,0)
var _search2=SEARCH("Successfully completed and left",MAX('Category'[Category]),1,0)
Return
CALCULATE([Measure name],FILTER('Category',_search1<>0||_search2<>0))

Or a measure as below:

_Total successful 1 = 
var _search=CALCULATE(COUNTROWS(FILTER('Category','Category'[Category]=MAX('Category'[Category]))),'Category'[Category]="Successfully completed and stayed" || Category[Category] = "Successfully completed and left")+0
Return
CALCULATE('Category'[Measure name],FILTER('Category',_search<>0))

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

5 REPLIES 5
v-kelly-msft
Community Support
Community Support

Hi @johnmelbourne ,

 

You may create a measure as below:

_Total successful = 
var _search1=SEARCH("Successfully completed and stayed",MAX('Category'[Category]),1,0)
var _search2=SEARCH("Successfully completed and left",MAX('Category'[Category]),1,0)
Return
CALCULATE([Measure name],FILTER('Category',_search1<>0||_search2<>0))

Or a measure as below:

_Total successful 1 = 
var _search=CALCULATE(COUNTROWS(FILTER('Category','Category'[Category]=MAX('Category'[Category]))),'Category'[Category]="Successfully completed and stayed" || Category[Category] = "Successfully completed and left")+0
Return
CALCULATE('Category'[Measure name],FILTER('Category',_search<>0))

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

CNENFRNL
Super User III
Super User III

Hi, @johnmelbourne , a fundamental alternative to your measure is like 

Total successful =
CALCULATE (
    [MeasureName],
    Category[Category]
        IN { "Successfully completed and stayed", "Successfully completed and left" }
)
AllisonKennedy
Super User III
Super User III

Hi John~

Sorry, I'm not following what you need? In English find where the .... did you mean to write more there?

Are you just looking to learn more DAX functions or is there a future/further business requirement that is inspiring you to change a measure that's currently working?


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hi,

 

Sorry, I only half typed it looks like.

 

 

In english, I am tryin to write a measure where it says find where the category contains the words "Successfully completed".

 

I just trying to learn a new way of summing values when a cetegory contains a part of a string.

 

So eg; what search ? / find ? / match ? DAX function would you use to calculate
Sum [Sales] where Product[ProductName] contains the word "health"

 

When product names might be

Health Gel

Health socks

General health

Physical health

Mental health

 

Any assistance appreciated.

You may try

Measure =
CALCULATE (
    COUNTROWS ( 'Product' ),
    SEARCH ( "health", 'Product'[ProductName],, FALSE () )
)

MATCH() isn't available in DAX native function lib. Similar to Excel, SEARCH() is not case-sensitive whereas FIND() is so.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors