Showing results for 
Search instead for 
Did you mean: 
New Member

Create measure to filter a column by what the element in the column starts with.

Short version:

I have a BI product and need to create a measure that filters out the row by the first value so the only values left start with 1. So it is something like :


measure = CALCULATE(SUM('Field'[Column], IF(LEFT[1: :])))


A little messy but you get the picture. 


Little more Detail:

I know what you are thinking, but putting a filter on a visual will not work. However, you may know a better way to do what I am trying. Below are a few elements in the column (named column a) of about 135K rows. The unique identifier is the first element in elements, specifically '1'. I need to filter all else out. This is because I need the numbers in the adjacent column (named column b). From here, column b's numbers will be added by another measure with a column c which is in a different field. b and c will be added into rows that share like values, in this case location codes. I was going to make this measure, another measure for column c (new measure 2 = SUM(2ndField,[column c])), and then a measure to add them together.  Column a below is formatted 123.4567, and to the right is b which is just a dollar number, which is what c is as well. 

BI 2.png

That is a lot and I know it is a little crazy. I'll teach these guys data management sometime. 


To summarize; filter column a by first element in elements to retrieve data from column b. Elements in column b will then be added to column c of a different field by rows that share location. 

If there is a better way, I am very open to suggestions. Thanks so much!

Super User
Super User

So why not just create a column like:

Filter Column = IF(LEFT([a],1) = "1",1,0)

Then any formula you need is just filtering Filter Column for 1

I have read the above through several times now and still can't really make heads or tails of what you are trying to do.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors