Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.