DAX Fridays! #6: FILTER

Learn how to use the DAX FILTER function to filter data.

NEXT VIDEO:-

You can apply filters in formulas that take a table as input. Instead of entering a table name, you use the FILTER function to define a subset of rows from the specified table. That subset then is passed to another function, for operations.

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP

[Noorfhd]

I love how you go through multiple example and explain each scenario thoroughly and most importantly how it behaves so we could think and not only implement. Thank you!

[czcttjm]

Nuevo suscriptor, agradecido por las enseñanzas.

[rolandkc]

Hum... I like this: DAX STUDIO and DAX FORMAT. With DAX STUDIO, I would now be able to experiment [play] with how FILTER works on its own and together with another function, like CALCULATE. Seeing the results quickly with different permutations of CALCULATE & FILTER would help me to assimilate that DAX technique [remember my previous comment on not fully understanding CALCULATE with FILTER and ALL functions]. I could also experiment with Segmentation with I am currently struggling with at work. Thank you, Ruth, for showing the way...

[cindyc]

Your explanation is excellent in particular going through multiple examples so it helps me to understand clearly with Dax Studio. Great. Thank you 😃😃😃

[markdawson]

Hi Ruth after watching this video many times, finally I followed you in DAX studio and my own Data set and it is finally going in to my very slow brain LOL, Great video you are a Star thank you so much .

[davidblake]

Hi Ruth, I know this post is old, but I am so happy. You have helped me solve a problem I have been working on for ages. I have watched most of your DAX Fridays clips - please keep up your great work they are most helpful. :-)

[marceloadelino]

Dear Ruth!! Hope this finds you well! First of all, congrats for the channel! It's been amazing to learn through your videos. I have a situation in which I am not able to handle in Dax. I need to create a running total through a calendar table, but eventually it turns negative. When it happens I need to keep the negative row, but the next row, I need it to start the running totals again. As it can happens several times through the calendar, the filter condition would have to be dynamic. I manage to solve through Power Query using List.Generate within a function, but I became extremely slow. And Dax has shown to be much faster. I appreciate any help you could provide. Tks for all!!!

[justFastk]

It's wonderful! Thanks a lot for these videos and for your great work! It's very useful and it really helps me to learn DAX for my job.
Thanks from Russia🇷🇺

[LotfyKozman]

Thanks for the video, Hope in the future you can provide some examples to include both the Filter() and Calculate() together using DaxStudio. Now there are thousands of videos show how each function works solely which is not very preferable.

[rrrprogram]

Great work Ma'am, Thanks for your efforts... its really enlightening...

[VinayKumar-ijeu]

Nice Video, but I have one question Ruth, when we use FILTER function in POWER BI it gives me this error " The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.". Can't we use FILTER function in POWER BI?

[mathewinmuscat]

Hello Ruth, can you share some videos on how to create a HR dashboard, especially current staff where you have date joined and date terminated in the same fact table. Your videos have inspired me. Thank you.

[haithamdarweesh]

Thanks for this video,
I have a question, what the difference between:
calculate (sum(tablename[price]), tablename[price]<100)
And
calculate (sum(tablename[price]), filtter(tablename, tablename[price]<100))

[kishoredubbasi]

Thank you for this great session, How to return the single column instead of entire table is it possible in measure calculation thanks in advance

[bradj]

Thanks again! Definitely harder to produce these examples in Power BI since EVALUATE isn't available. Have to create tables I guess (GROUPBY, SUMMARIZE, etc.)

[dbradley]

Hi, Ruth. AND function seems more simple than using two filter functions. Is there an advantage to one or the other? Thanks for making these videos!

[keithaa]

Thank you for your videos. They have been so helpful.

I have tried to re-create this example in Power BI using the New Table function to test the data. It works great but I have a question.

FILTER only seems to be able to see the columns from the filtered table.

If I add in a column from a related table, it won't show the fields, even if there is a many to one relationship with the correct directional flow.

FILTER('Product Data',
'Product Bikes"
&& 'Sales Data'[Unit Price]>1000))

Perhaps you could do a FILTER video in Power BI instead of excel?

[olalaarden]

Is there a difference between example query4 & 6? Both have 2 filter criteria right? Are they just different ways of writing DAX function?

[macm]

You're Awesome! Thanks for all DAX Friday videos. I was playing around FILTER function by creating tables after watching your video however I'm not sure how to determine that which function suitable for what(Measure, Calculated column or table) Here you used DAX studio so I'm not able to figure out what to create(Measure, Calculated column or Table) using the FILTER example you shown in this video. I tried creating Measure, calculated column and Table using your example and not sure why I only got succeed in creating Table.

[Wzxxx]

Does it mean that filter expression also iterates like sumx? You put as and argument expression "price - cost" so as far as i know it must itarete the table row by row in order to make this calculation first and get result to be filtered. Then it checks for the 2nd filter expression (mountain bikes). Is it true?