cancel
Showing results 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.

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### 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 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.

Top Solution Authors
Top Kudoed Authors