- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Measure with contains and starts with

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2018 06:49 AM - edited 04-13-2018 07:32 AM

Hi all,

I need a measure with a calculation of Revenue with some filters but i am not sure how to do this. I know this is possible in the visual filters, but I need to add another calculation to this one after it that is why I need it in a measure.

I need the sum from Revenue

Which contains or starts with "BV" from Order

And starts with "PA" from Note

I was trying something with CALCULATE(SUM( )) and FILTER and FIND() but obviously not getting the results I want.

Below the sample data, at the end I only need the €61, €23 and €65 below.

Revenue | Order | Note |

€ 123,00 | BV45 | GF23 |

€ 61,00 | BV46 | PA143 |

€ 48,00 | OR456 | CD32 |

€ 65,00 | OR457 | PA32 |

€ 45,00 | OR458 | PA124 |

€ 23,00 | BV47 | PA543 |

€ 65,00 | BV48 | PA234 |

€ 56,00 | BV48 | CD42 |

€ 43,00 | ORD459 | RG43 |

€ 23,00 | OR460 | RG34 |

€ 43,00 | BV46 | CD52 |

Thanks in advance!

Solved! Go to Solution.

Accepted Solutions

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-17-2018 08:06 AM

Here it is,

CALCULATE(AVERAGE(TableName[Revenue]),FILTER(TableName,LEFT(TableName[Order],2)="BV" && LEFT(TableName[Note],2)="PA"))

All Replies

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2018 07:31 AM

Sample data please.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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

Proud to be a Datanaut!

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-12-2018 07:52 AM

Done! hopefully this makes it more clear.

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-17-2018 08:06 AM

Here it is,

CALCULATE(AVERAGE(TableName[Revenue]),FILTER(TableName,LEFT(TableName[Order],2)="BV" && LEFT(TableName[Note],2)="PA"))

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-20-2018 01:02 AM

Thanks alot! I was actually pretty close!

## Re: Measure with contains and starts with

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-20-2018 04:30 AM

The solution provided earlier was if BV and PA are at starting position and here is another one if they fall between the text.

CALCULATE(AVERAGE(TableName[Revenue]),FILTER(TableName,FIND("BV",TableName[Column 1],1,0) > 0 && FIND("PA",TableName[Column 1],1,0) > 0))

check the brackets if i missed any at closing and hit some kudos if this worked and helped.

Thanks