DAX Fridays! #6: FILTER

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

[@markdawson8876]

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]

+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]

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]

+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]

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]

+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

[@cindyc6703]

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

[@GustavoHeliAliagaGalvez]

Nuevo suscriptor, agradecido por las enseñanzas.

[@davidblake6356]

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]

Wonderful to hear!

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

[@CurbalEN]

+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

[@justFast1k]

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]

And many thanks for the feedback too!

[@herbm5903]

Thank you Ruth. Very useful!

[@CurbalEN]

You welcome! The pleasure is mine :) /Ruth

[@rrrprogram8667]

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

[@CurbalEN]

+RRR program You welcome ;) /Ruth

[@marceloadelino5838]

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

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

[@CurbalEN]

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

[@CurbalEN]

+Lofty Kozman Hi Lofty, the Calculate video with filter is available here: kzbin.info/www/bejne/q4uUZnRnaKamfrs Have a great weekend! /Ruth

[@nawerdarbi]

Thank you so much!

[@stevennye5075]

very good explanation

[@CurbalEN]

Glad to hear :) /Ruth

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

[@CurbalEN]

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

[@kishoredubbasi9780]

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

[@bradj229]

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]

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

[@bradj229]

@@CurbalEN Thanks Ruth!

[@bradj229]

@@CurbalEN Thanks Ruth!

[@LucasFernandes-ri4xj]

Excellent clarification. Congratulations!

[@hajeraabdullah9157]

you're a genius!

[@CurbalEN]

😊

[@olalaarden6601]

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

[@dbradley9078]

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!

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

[@samyrG26]

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

[@CurbalEN]

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]

@@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]

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

[@samyrG26]

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

[@CurbalEN]

PP utilities for excel /Ruth

[@lasanthavidyarathna5674]

love it

[@macm7730]

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]

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

[@macm7730]

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

[@MohabAboushady]

You are the best..

[@CurbalEN]

😊 /Ruth

[@rohitkolekar5981]

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

[@VinayKumar-ij4eu]

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]

Have the same problem, have you solved it?

[@haithamdarweesh8781]

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

[@CurbalEN]

Hi! I have a video on that: m.kzbin.info/www/bejne/m6jNg3yqgdGafZo /Ruth

[@jonaskarlsson477]

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.

[@tymothylim6550]

Thank you for the video :)

[@krishnaram789]

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

[@attereco]

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!

[@keithaa7444]

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]

+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

[@Hustling_Learning]

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

[@CurbalEN]

Yes

[@walidkhlil2872]

thanks

[@jasonwoodward5501]

Wow! Thanks

[@CurbalEN]

You are on 🔥🔥! /Ruth

[@krishnaram789]

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]

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

[@krishnaram789]

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]

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

[@gaid81]

Fantastic

[@CurbalEN]

+AHMAD GAID Thanks Ahmad :) /Ruth

[@solarson_studio]

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.

[@harish7548]

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

[@jereyeses]

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]

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

[@venkatareddykummitha5863]

hai difference between summarize vs group by + parameters vs filters

[@rachelramphal9464]

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?

[@denisekresge8017]

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?

[@SamehRSameh]

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

[@Prasoon.Chauhan]

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

[@CurbalEN]

Look for Dax Fridays : CALCULATE /Ruth

[@blueroqltd387]

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]

Thanks , glad to hear that ! You can only use wildcards with SEARCH: m.kzbin.info/www/bejne/rJO0k4R4jZeHbqM /Ruth

[@rudayreddy]

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

[@CurbalEN]

Sure! /Ruth

[@SiddharthSharma21]

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]

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

[@abhisekmondal4076]

Hi Mam, Can we get the source data please.

[@CurbalEN]

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

[@jeewantham]

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]

Could you please provide Excel file, thanks in advance!

[@CurbalEN]

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

[@BigDataSport]

thanks! Ruth!!

[@CurbalEN]

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

[@shorn77777]

why no one complements her accent.

[@CurbalEN]

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

[@shorn77777]

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]

+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]

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]

+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

[@davidrf4199]

How to use "FILTERS ()". Not "FILTER". More yes "FILTERS ()".

[@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?