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 IX
Super User IX

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

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!

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

Proud to be a Super User!

Helpful resources

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.


Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors