DAX Fridays! #6: FILTER

Подписаться 135 тыс.

Просмотров 70 тыс.

50% 1

Learn how to use the DAX FILTER function to filter data.
Link to PBI file: curbal.com/blog/glossary/filt...
PREVIOUS VIDEO: • DAX Fridays! #5: CALCU...
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.
Looking for a download file? Go to our Download Center: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo
► Google +: goo.gl/rvIBDP
► Facebook: goo.gl/bME2sB
► Linkedin: goo.gl/3VW6Ky
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP

Опубликовано:

29 сен 2016

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 109

@Noorfhd 4 года назад

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!

@cindyc6703 3 года назад

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

@user-cz9ct8tj7m 10 месяцев назад

Nuevo suscriptor, agradecido por las enseñanzas.

@davidblake6356 4 года назад

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

@CurbalEN 4 года назад

Wonderful to hear!

@herbm5903 6 лет назад

Thank you Ruth. Very useful!

@CurbalEN 6 лет назад

You welcome! The pleasure is mine :) /Ruth

@rolandkc 7 лет назад

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

@CurbalEN 7 лет назад

+Roland Kc You are going to love DAX Studio is a fantastic tool! I learned ( and I am still learning) the hard way, so it is a real pleasure if I can make the way smoother for others 😄 /Ruth

@rrrprogram8667 7 лет назад

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

@CurbalEN 7 лет назад

+RRR program You welcome ;) /Ruth

@tymothylim6550 4 года назад

Thank you for the video :)

@justFast1k 3 года назад

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🇷🇺

@CurbalEN 3 года назад

And many thanks for the feedback too!

@nawerdarbi Год назад

Thank you so much!

@marceloadelino5838 2 года назад

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

@lasanthavidyarathna5674 3 года назад

love it

@markdawson8876 7 лет назад

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 .

@CurbalEN 7 лет назад

+Mark Dawson Hi Mark! If it helps I had to go through the same painful process myself ! Perseverance is the key to master DAX :) /Ruth

@markdawson8876 7 лет назад

Hi Ruth I am hoping you have some influence with Microsoft , I have Just upgrade to Home Office 365 £60 . I now find out 365 does not support Power pivot and Power view , I had access to both with 32 bit 2016 Excel , how can they call this an upgrade , please see the complaints about no power pivot on the web . I know there is little you can do , but at least I feel better now LOL . Mark

@CurbalEN 7 лет назад

+Mark Dawson Hi Mark! I dont think I have a big influence but I know we as a Community do have it. I didnt know they removed Power Pivot and Power View from excel, that is a disaster! I am not happy about the last changes they made and be sure I have and will complain about them, but it is more powerful if we all do it together! I am in! I am off to google to search about this... Thanks for letting me know ! /Ruth

@markdawson8876 7 лет назад

Hi Ruth , Microsoft have refunded my money , But I need to have power Pivot and Power view , So I have gone for the ProPlus which has cost me double the money , feel like I am being held to Ransom , thanks for listening . Have a good evening Mark :-)

@CurbalEN 7 лет назад

+Mark Dawson Truly sorry to hear that, it makes me sad too :( Why don't you use Power BI Desktop until Microsoft fixes its own mess? /Ruth

@jasonwoodward5501 6 лет назад

Wow! Thanks

@CurbalEN 6 лет назад

You are on 🔥🔥! /Ruth

@MohabAboushady 5 лет назад

You are the best..

@CurbalEN 5 лет назад

😊 /Ruth

@hajeraabdullah9157 4 года назад

you're a genius!

@CurbalEN 4 года назад

😊

@stevennye5075 5 лет назад

very good explanation

@CurbalEN 5 лет назад

Glad to hear :) /Ruth

@LotfyKozman 7 лет назад

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.

@CurbalEN 7 лет назад

+Lotfy Kozman Hi Lofty, next week video will show just that, stay tuned! Ruth

@CurbalEN 7 лет назад

+Lofty Kozman Hi Lofty, the Calculate video with filter is available here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-uZ15A13AsHY.html Have a great weekend! /Ruth

@gaid81 7 лет назад

Fantastic

@CurbalEN 7 лет назад

+AHMAD GAID Thanks Ahmad :) /Ruth

@LucasFernandes-ri4xj 4 года назад

Excellent clarification. Congratulations!

@jonaskarlsson477 2 года назад

If i want to use this i PowerBI is this filter a Messuarement or where do i insert the filter() i did this Aktiva = FILTER(TestTabell,TestTabell[startdate].[År] = SELECTEDVALUE(tblDimDatum[År])) ERROR: Uttrycket refererar till flera kolumner. Det går inte att konvertera flera kolumner till ett skalärt värde.

@dbradley9078 4 года назад

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!

@krishnaram789 5 лет назад

Hi Ruth thanks for the video i have a one question , how can apply same query in power bi because when i tried to same thing i got a

@walidkhlil2872 4 года назад

thanks

@kishoredubbasi9780 3 года назад

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

@attereco 3 года назад

Hi Ruth! What if I would need OR with more than 2 arguments? I receive this message: "Too many arguments were passed to the OR function." I tried to nest OR but still got the same error message. Thanks so much for your help!

@VinayKumar-ij4eu 2 года назад

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?

@jonaskarlsson477 2 года назад

Have the same problem, have you solved it?

@Wzxxx 2 года назад

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?

@haithamdarweesh8781 6 лет назад

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

@CurbalEN 6 лет назад

Hi! I have a video on that: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ewjRItLlgG8.html /Ruth

@solarson_studio 4 года назад

Hi Ruth, it was a great video to learn FILTER DAX. In terms of this video, i have a question. When i follow the step to practice the same steps. When i open the new Query dax for test the new measure. After connected with Excel data, there are two tabs coming out (one duplicated Query DAX also created). Do you know why this happens? Thanks.

@keithaa7444 6 лет назад

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 Data'[ProductCategory]="Mountain Bikes" && 'Sales Data'[Unit Price]>1000)) Perhaps you could do a FILTER video in Power BI instead of excel?

@CurbalEN 6 лет назад

+KeithA A Definately, The behavior should be the same though... Put some sample data in the power bi community to get some help about your specific case. /Ruth

@mathewinmuscat 5 лет назад

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.

@CurbalEN 5 лет назад

I don’t have any dataset where I can share the data I am afraid. /Ruth

@rohitkolekar5981 3 года назад

Can we use AND condition instead of Nesting filters ? Specifically for Price SEK and ProductCategory ?

@blueroqltd387 5 лет назад

Hi Ruth, would you apply a wildcard to capture/filter productCategory = "*Bikes"? By the way, your channel has been an absolute revelation for my BI work

@CurbalEN 5 лет назад

Thanks , glad to hear that ! You can only use wildcards with SEARCH: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vbQbQBX2T8A.html /Ruth

@samyrG26 5 лет назад

Thank you so much Ruth. Dax studio is the best to manipulate dax? Something like notepad++ can do the work?

@CurbalEN 5 лет назад

It depends on what you are trying to do. For troubleshooting performance issues is the best tool out there unless you want to do it manually. /Ruth

@samyrG26 5 лет назад

@@CurbalEN in fact, I am pretty new in Power BI and DAX so I think Daxstudio will help me a lot as you shown in this video.

@CurbalEN 5 лет назад

Go for it! It is a fantastic tool :) /Ruth

@samyrG26 5 лет назад

@@CurbalEN any additional tools for Power BI and DAX?

@CurbalEN 5 лет назад

PP utilities for excel /Ruth

@olalaarden6601 3 года назад

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

@bradj229 2 года назад

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

@CurbalEN 2 года назад

For table functions, use the “create table” button, that way you don’t need a external tool

@bradj229 2 года назад

@@CurbalEN Thanks Ruth!

@bradj229 2 года назад

@@CurbalEN Thanks Ruth!

@rachelramphal9464 3 года назад

How can you do filter with a summarize? I want to filter a column for certain months, but I don't need all the columns in the table, how could I do this?

@jereyeses 6 лет назад

GRACIAS RUTH, UNA PREGUNTA, Y ¿APLICA PARA FECHAS? QUÉ PASA SI QUIERA FILTRAR POR LA ULTIMA FECHA O LA PRIMERA? GRACIAS DE NUEVO, SALUDOS DESDE MÉXICO.

@CurbalEN 6 лет назад

+Jesús Reyes Hola Jesus, Para fechas es mejor usar las funciones de time intelligence, como dateadd, YTD, etc.. Buen fin de semana :) /Ruth

@macm7730 3 года назад

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.

@CurbalEN 3 года назад

Filter returns a table as a result, therefore you can only use a table in power BI!

@macm7730 3 года назад

@@CurbalEN Got it. I'll keep this in mind. Thanks for the reply :)

@krishnaram789 5 лет назад

Hi Ruth thanks for the video i have a one question , how can apply same query in power bi because when i tried to apply same quires(only filter function) i got a errors in power bi desktop so can you u please help and if u have any video please share. thanks

@CurbalEN 5 лет назад

It should work in pbi too. Can you report the errors in the pbi community?

@krishnaram789 5 лет назад

I used this query in pbi "Red color = FILTER(DimProduct,DimProduct[Color]="Red") " i got below warning the expression refers to multiple columns. multiple columns cannot be converted to a scalar value.

@SochBhai 5 лет назад

@@krishnaram789 Filter doesn't return a scalar value that's why your getting this error.

@SiddharthSharma21 5 лет назад

Hi Ruth, I am getting an error saying "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." My columns are Order_details[UnitPrice] which contains price for products. I wanted to filter products which are above 20. But, unfortunately I cannot. Below is my code: FILTERED COL = FILTER( Order_Details,Order_Details[UnitPrice]>20) Thanks.

@CurbalEN 5 лет назад

It means that your measure is returning a table when it expects a value back. Post your measure and some sample data in the power bi community to get detailed help ! /Ruth

@tantipornanan Год назад

As i learn from one online course for BI, It has been mentioned about filter which will make ton of overload memory so we should avoid that. Is that true? if yes, what should i use instead?

@CurbalEN Год назад

If you are a beginner and your data is small, you can use filter as much as you like :)

@tantipornanan Год назад

@@CurbalEN Thank you so much, if there are so much of data i have to find out. :)

@SamehRSameh 3 года назад

I use distict count function ,, but i want to appear average in total ,, how ????

@venkatareddykummitha5863 2 года назад

hai difference between summarize vs group by + parameters vs filters

@Hustling_Learning 3 года назад

Is this also implemented in power bi, like the same way you implemented this in your video...?

@CurbalEN 3 года назад

Yes

@harish7548 3 года назад

We can get results without using filter function what is exact difference

@denisekresge8017 5 лет назад

Hello, I need to create a filter that has more than two criteria or conditions. What would I need to do if I have more than two criteria or conditions? Is there a video you already created that would show me?

@Prasoon.Chauhan 4 года назад

if i want to know the product in 2015, how i use filter for year??

@CurbalEN 4 года назад

Look for Dax Fridays : CALCULATE /Ruth

@rudayreddy 5 лет назад

Hi Ruth, Is it possible to make video on DAX function - FILTERS

@CurbalEN 5 лет назад

Sure! /Ruth

@jeewantham 4 года назад

how we filter more expression: i want to count rows of more expression. All to gather how many rows have in expression 1, 2 , 3, ,,,,,,,, etc.

@BigDataSport 7 лет назад

Could you please provide Excel file, thanks in advance!

@CurbalEN 7 лет назад

Hi David, Here you have it: gofile.me/2kEOD/GDb9WUozG /Ruth

@BigDataSport 7 лет назад

thanks! Ruth!!

@CurbalEN 7 лет назад

+David Fombella Pombal You welcome David! :) /Ruth

@abhisekmondal4076 4 года назад

Hi Mam, Can we get the source data please.

@CurbalEN 4 года назад

All my source files are available at Curbal.com > resources > download center. /Ruth

@shorn77777 6 лет назад

why no one complements her accent.

@CurbalEN 6 лет назад

+sharon mathew Hi Sharon, my swenglish-Spanglish accent ? ;) /Ruth

@shorn77777 6 лет назад

are you Swedish, Spanish, i thought kind of from East of Europe. i live in Dxb. but i like your accent, its sweet as 16 yrs. or 23 or 24ish

@CurbalEN 6 лет назад

+sharon mathew Spanish from birth, now I guess a mixture of both. Regarding my age, double that and you’ll get it right... /Ruth

@shorn77777 6 лет назад

i have/had some friends from East of Europe, i loved them, some of them i still do. you remind me of some of them. i mean the way you speak, with your sexy broken accent. so, i had requested you something earlier, i dont know if you received it. i was in search of a very difficult feature. then, to be honest, i underestimated everyone because i already know its not easy to obtain. until i heard from you that microsoft gave you an award or so. then i was like, whattt!. wooow, so if i am not asking you too much on the topic, give me atleast a bit of ideas how to go about. a direction is much more than what i can ever expect from anyone at this point. because the requirement is almost impossible requirement. its how to bring previous row field items on each row and then further to be available for further calculations. for instance, i want to know the difference between the dates in the same column but across the rows. could you think of some lead and give as in when you become free. and thanks,

@CurbalEN 6 лет назад

+sharon mathew Hi Sharon, I think this is what you are looking for: community.powerbi.com/t5/Desktop/Calculate-duration-based-on-dates-in-different-rows/td-p/58886 /Ruth