DAX Fridays! #78: When should I use FILTER() with CALCULATE?
Vložit
- čas přidán 29. 06. 2024
- When should you use the filter function with CALCULATE?
As you probably have noticed, sometimes people use a column in the filter part of CALCULATE and sometimes they use the FILTER function.
The question is, when should you use what?
In this video I will try to clarify that question.
Dont miss part 2 where we talk about KEEPFILTERS: • DAX Fridays! #79: KEEP...
Download the file here: curbal.com/blog/glossary/calc...
Here you will find all the previous CALCULATE and FILTER function tutorials.
curbal.com/blog/glossary/calc...
curbal.com/blog/glossary/filt...
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
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
#DAXFRIDAYS #CURBAL #DAX #POWERBI #MVP
Great ruth, I am also filering the fish (first slide 😂)
😂😂 Fun noise right? Diving Deep into DAX, i thought it was appropiate ;)
/Ruth
Hi Ruth, calculate with own filter ( not function) is same as calulate with function filter and function All.
True, i should have Said that, shouldnt I? Pin your post for visibility :)
/Ruth
My favorite DAX instructor on youtube!
Yey!!! Thanks 😊
/Ruth
I’ve watched many of yours and each time, I’ve learned a lot. Thanks a lot sincerely !
Your videos are really helpful and informative, Ruth! The way you use examples to demonstrate differences in these hard-to-grasp concepts is simply remarkable. Thank you from a great fan of yours :-)
That's soooo cute the way you explain things! Thanks for your vids Ruth. Cheers
😂 cheers Willian!
/Ruth
I think I gotta start taking notes on your videos for a quick glance for myself. Your videos have always been consistently good even it was produced many years ago. Thanks, Ruth!
Thanks Alex!
Hi there!! Ii am glad to see you sharing with all of us your expertise in the excel Power Tools!!!
🥳🥳
/Ruth
Great Ruth !! Best demonstration for filters so far!!!!
🥳🥳🥳
Yey!
/Ruth
Hi, Ruth, I am beginner in power bi. Every time I watch your videos to clear my concept, you always rocks. Thanks and keep it up.
Thank you!
Eres fantástica Ruth! Soy muy fan de tus tips y procedimientos.
Gracias por todo el material tuyo que hay en la web!
Todo un placer!!
/Ruth
Thanks Ruth. This is great stuff for the learners. I have taken a paid course from very renowned trainers and they also haven’t clarified it. May be they expect me to know in advance. Thanks again.
Many thanks for providing useful information and clarifying the concepts.
You welcome and hope it helps you further in your understanding!
/Ruth
Thank you Ruth. I’ve been trying to understand this for a while and have searched with little success. Really clear explanation. Many thanks. Simon.
Thanks for creating this video. Very helpful. I wasn't aware of the FILTER vs no FILTER distinction in CALCULATE.
One step closer to mastery!
/Ruth
That’s a very important concept! Good work!
Thanks for the feedback!
/Ruth
Best video of all I'm seen in search of calculate explication. Thanks
Glad it helped!
Thanks for the explanation. Very nicely explained :)
Oh yay! I think that might have been my question :). Thank you!
Yes it was! I couldn’t find you in the comments again, search on YT is not the best oddly enough..
Hope I manage to answer your question!
/Ruth
Great! Thank you for the tutorial, very useful!
Excellent and thanks for the feedback!
/Ruth
Great and Easy way to explain this.
fantastic explanation, great video like always.
Glad to hear :)
/Ruth
Thank you, that cleared a muddy understanding of calculated worked for me.
The way you pronounce your name ...loved it really loved that accent😀😎
Muy buuen tutorial, gracias Ruth
Gracias Ivan!! :)
/Ruth
Thank you Ruth!! It's really helpful... :)
I really hope so!
/Ruth
Such a clear and concise explanation as always.
Thanks Jack and happy Sunday :)
/Ruth
Your videos are awesome!!
Thank you!
A lot of thanks from Egypt ♥
Thanks mam. I have learned at last how filter behaves
Thanks for the great video Ruth !! My marker was always: FILTER filters the table behind the scenes and CALCULATE overwrites it. Have a great weekend !
Great tip!! Pinned för visibility.
And happy Friday :)
/Ruth
Hi Ruth
Thanks for the great video!
Is there a way to present the Tables (and charts) in Right to Left view?
Could not find any info. For the right to left lengugages. Such as Hebrew or Arabic.
Will Happy to read your answer.
Thanks!
Hi!
According to this:
ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8267295-right-to-left-chart-and-dashboards-supports
It is already done, but for what I can see on google it is not supported yet.
Your best bet? Vote on the ideas.powerbi.com and promote so others vote too.
Happy Friday!
/Ruth
Curbal Thanks a lot!
Have a great weekend😎
Same to you :)
/Ruth
You have explained it in a very detailed manner. Thank you 🌷
And thanks for the feedback! 😊
/Ruth
Thank you Ruth, very good explanation, ,,,,,, if we want to see the same behavior as the calculate function in the filter function (Ignoring the filter context) we add the ALL function, and this is how the calculate filter internally work:::
Salaries Pending w filterALL = CALCULATE([No Employees],
FILTER(ALL(Employee),Employee[Status]="Terminated"),
FILTER(ALL(Employee), Employee[Dept.]="LA"))
Awesome, thanks for sharing!
/Ruth
Actually,
Salaries Pending w filterALL =
CALCULATE(
[No Employees],
FILTER(
ALL(Employee[Status]),
Employee[Status]="Terminated"
),
FILTER(
ALL(Employee[Dept.]),
Employee[Dept.]="LA"
)
)
So if I want my result to be dynamic, I really need to add FILTER function(s) in the CALCULATE function.
Thanks for pointing out the difference with and without FILTER, Curbal!
Great as alway, Thanks
Thank you! Helped me today! :)
🎉🎉
Thank you so much for the Video
You welcome Mark, have a great Sunday :)
/Ruth
You are my hero!
🥳🥳
Great video
This is great. thanks
Thanks for this video, a small clarification obout CALCULATE with Booleans;
(more than one column in Boolean) Conditions of (> >= < = CALCULATE (expression; FILTER (table; AND(ALL(Boolean column1); ALL (Boolean column2)))
because The filter arguments of CALCULATE (from the second parameter onward) are evaluated in the original filter context.
according to that,
CALCULATE + Boolean (column) = CALCULATE ( FILTER ( ALL (‘Table’[column]) ) )
Hope this helps,
Regards!
Great video 😁👍
🥳🥳
It’s Saturday for me when I’m watching this
Well done!
U r a life saver
At ur service!
Great and easy way ,thansk
Thank you very much
Hola Ruth, que tal tengo un par de consultas con respecto al objeto Matrix de Power BI , quería saber si puedes:
- Darle formato a cada uno de los encabezados de forma independiente: Cambiar el color de fondo o el color de fuente
- Solo habilitar la opción de icono +/- solo para algunos encabezados.
Muchas Gracias por la ayuda
Interesting !!! Ruth I would like to know if it is possible to use a filter for specific tables in the dashboard/report?
Dear Ruth, Thank you very much for the wonderful tutorials you have on DAX, really appreciated.
I need some help :) I need a little more explanation of using slicers with Calculate Function and Filter Function.
In this video, which you used the slicer, the function, which did not have the filter function, did not listen to the slicer, and the function which had the filter function did.
However, i have another file, in which, when i user a calculate function (without a filter function within) and use a slicer, it works, I mean, the slicer does affect the output.
for Example:
Calculate Red Sales = CALCULATE(
[Total Sales],
'Product'[Color]="red"
)
f_Calculate Red Sales = CALCULATE(
[Total Sales],
FILTER('Product','Product'[Color]="red")
)
When I used the slicer on Product Category, both of them changed to give me the same output.
So, if you could give me an example of using Calculate, FIlter With Slicers, would really really appreciate, or if there is a video which explains the same.
I also, checked the other calculate video's but did not get the answer to my question.
Thank you very much for the wonderful work done.
Best Wishes
Ruth, :)) I think I got the answer.
If I filter on the basis of a column, which has been used in the dax, that specific filter will get ignored, since the filter mentioned in the calculate will override it
But, if I filter on another column, which is not mentioned in our DAX formula, that will be accepted.
This example, is given under the CALCULATE video.
Have i got the explanation correct ? { Thanks Again }
Have you seen this video?
:)
/Ruth
Dear Ruth, Thank you for your prompt reply. Yes, indeed i saw the tutorial.
Then after watching and practising 2-3 times, it worked.
My query is resolved.
( I think while writing the entire msg to, it clicked what was going wrong)
Thank you for the amazing tutorials and explanation.
Keep up the amazing work. :))
Best way to learn!! :)
/Ruth
:)
excellent!
Thanks!
/Ruth
Hi Ruth! Many thanks for the explanation of the Filter function. I tried to apply it by excluding the zeros in the median. However this does not work: Median= CALCULATE(Median(TableName[SaleAmount]), FILTER(TableName, TableName[SaleAmount] 0 )) . It always says that there are too many arguments. Do you have any idea why? Many thanks for your help in advanced
Thanks Ruth.. does the sequence of the filter expressions matter?
Hi Ruth!, thanks for all your videos!!
I have a doubt, can i use a wildcard when i use calculate ?? something like "pend*" when i want to filter all the words that start with "pend"?
Saludos! Arica-Chile :)
Hi there, very informative, Have one concern. What if I want to use OR function in Filter? in above example we have used 2 filters, 'Status; AND 'Department' . Which mean we are indirectly using AND function using 2 filters. What if I want to use OR... For example , Status is Pending OR Terminated.. How should I get these details
thank you
Hi,
I am trying to generate previous month sales in a matrix view.
When I use PMSales = CALCULATE([TotalSales],FILTER(Datestable,DATEADD(Datestable[Date],-1,MONTH))) I can see current month values only. However when I use PMSales = CALCULATE([TotalSales],DATEADD(Datestable[Date],-1,MONTH)) it displays the previous month sales. What is the difference between the two measures??
Great. one question. Does FILTER also ignores Report/Page level filters?
Starts at 1:35
Again, thanks Ruth. Can you also do videos on using python & R with power bi?
Not good enough in those languages to make videos about it, but when I learn something I always show it here. There is an R and Python playlist, check it out!
/Ruth
Hi Curbal
Very useful video! Is it possible to have the link of the dax function page you share in this video
Hi, just curious. Can you create a slicer that filters 2 values?
Lets say I have a STAtus column with 0,1,2 data.
My slicer says Active, Pending, Inactive.
When i select Active it should select all 2 and 1 status.
When i select Pending it should filter all 1 status.
When i select Inactive it filters all 0 status.
Thanks
Dear Ruth, When we say, the filter argument, which is within the calculate, has to return a
Number
Text
Date
And can not return a Table.
But, suppose I filter, where dept="sales"
Won't I get an entire Table, in return, where the dept="sales"
Thanks
I think what she was saying is that the Calculate function (not the filter) has to return a number, text or date. Filter arguments in a Calculate function can be the following:
1. Boolean condition (Ex: [dept]="sales')
2. A one column table (Ex: ALL(DeptTable[dept]))
3. All rows for one table (Ex: FILTER(ALL(DeptTable), [dept]="sales"))
Thanks for replying, I completely missed this comment!
/Ruth
Thanks. Appreciated
Ruth, I tried this with VAR I was getting error. Just try replacing all filter with Variable.
=') this very useful helpful. thanks
You can use measures inside calculate function
Can you make a video on how to filter a specific column from a multicolumn table or the opposite not making that specific column change when filter is applied. Thanks
Thanks
How can I do if I need a measure that lets me filter by measure?
I have a measure that needs to be a measure because is dynamic depending on parameter.
Now i want calculate the count of values > 0 for that measure and i cant figure it out
I need it to be a measure because i then use that total count for other calculations.
I would like to add that when you need to combine filter expression + USERELATIONSHIP you mostly need to use version without FILTER, otherwise your measure will return null.
Is it possible to divide a column obtained with the calculate function by the Summarized total of the same column?
How can we copy any column from one table and past in another table in power Bi
is the first argument in the filter function is the table on which the measure in the filter is calculated?
Nice video, i found it weard that calculate when not using FILTER, does not filter the result when using a filter visualization
How about if I want to create a measure that returns names of employees with pending status?
Hi Curbal, how do I use calculate to compare 1 value against the whole table? eg, an agent score (filtered at page level) against all agents in the table. table(sales) structure looks like this: AgentID, Score, DeptID, TeamID
Hi Raks, can you post the question in the power bi community? Make sure you post some sample data too!
/Ruth
Great Ruth! :)
Is is possible to count No Emploees where Tot Salaries is greater then 15000?
Ho to do it?
Regards
Hi Ruth, can you talk about how and when to use a table buffering in Power query?
Yes, of course! Long overdue on that one!
Great video as always !
I have a question though: Is there a way to filter by using only part of the value and not the whole word? For example instead of finding all products for which [Color] ="Red", can we find all products for which [Color] contains the letter "R" somewhere in the color field?
Not yet :(
How can we do that to get top 5 salarys based on status and location of table
Hi
Need help with dates in different tables . If the campaign date greater than order date .. sum of sales otherwise blank
Hello,
big fans of your lectures
thanks
i am facing one issue where my client want to display last 5 finacial qaurters including current quarter.
their financial commence from feb to jan
so if current fy quaerter is 2020 Q3 the. data in table should be for 2020 Q3, 2020 Q2, 2020 Q1, 2019 Q4 , 2019 Q3 and corresponding measure in table
can you please help me with this or if you have uploaded any video for such scenario plz let me know
Thank you
Came here for THE lindo dax, but stayed for the cute misspelled "devilered" (7:05).
I have two many fingers in my hands 😂😂😂
/Ruth
How would i do a starts with filter?
Hi Ruth, would you be so kind as to share a link to where you got your T-shirt from. I just love that t-shirt :)
I got it as a gift
Hi Ruth, thank you for your lovely videos. I am trying to count the number of distinct cities where profit is greater than 100,000. When I use the calculate function, it does not aggregate the profit over distinct cities, what is the work around? In sql it will be aggregate by sum of price and group by city and having clause having sum(price) > 100000.
It is probably an issue with your model. Could you ask in the power bi community? Give as many details as you can :)
/Ruth
Hi Nedex, did you resolve your issue. I am having same issue and would be delighted to know how you got on..
How do I filter with wildcards (SQL 'like') ?
How can I filter all except 2 countries for example, I know that you can do it with "", but only for 1, but if I want all except those 2 countries it would be like for example: calculate(sum(values);filter(tablename;table[country]"China" and "Us"))??, something like that, please help, thank you.
Ok, somehow i will learn how to use CALCULATE function, but tell me please, how did you know i am watching this video on friday 😁
can we able to filter more than 2 values in one column using DAX, which is similarly like multiple selections made using list slicer.
Yes, you can.
The power bi community is the perfect place to get individual help. Give as many details as possible of your case to get help.
/Ruth
Hi, you say the filters cannot return a table but FILTER function returns a table…I am confused
Can anyone explain what calculation you have in No Employees column? is that an Employee Id that you have ?
Hey Ruth if we want to calculate a value based on dept for example what is the salary paid for MES dept how can we do that using a measure.
Hi!
Could you post in the power bi community? Make sure you give as many details as possible to get help quickly!
/Ruth
SumDept =
SUMX (
FILTER ('Table' ; 'Table' [Dept] = "MES");
'Table'[SalariesAmounts]
)
what if I have a table where each Customer ID has a record of everything they purchased in a given month. I want to measure the revenue of users in August, that do not have a record in September. Anyone have any idea here?
Ruth, please enable paid membership in the channel. You've helped me so much that I feel bad for not retributing, even just a little
I had something prepared when I reached 30k subs (it was the limit then) but I never turned it on.
I will now, thanks!
"Please Microsoft"--it touches my heart when Ruth pleads with Microsoft.
I am a hopeless optimist and a dreamer so assuming that Microsoft watches my videos and that pleading to them would work it is right up my alley 😂😂😂
/Ruth
Hi Ruth, can you please explain what lock aspects does when turned on..i didn't see any changes, could you please help on it?
Hi Kalias, i am not sure What you mean, can you please rephrase?
/Ruth
In visual properties lock aspect option is there? Not sure what's goal it atains when turned on
Ok, got it!
Lock aspect means that if you change one dimension (height or
width) of a visual, the other dimension will
automatically be changed to maintain the shape and proportions of the
object.
Hope this helps!
/Ruth
Thanks Ruth!!☺️
My pleasure :)
/Ruth
Hi Ruth
I appreciate your work. I am trying to follow along ... can I ask what the formula measure is for [No Employees] please.
Thanks
Hi Tim,
No of employees is COUNTA(Employees (name)), I will add the download file in a hour .
Of course, if you want to count the number of employees on your file use DISTINCTCOUNT instead so you get the right number.
/Ruth
Perfect! Thank you
It will take a bit longer to add the file, I am out and about, but as soon as I can :)
/Ruth
The file is now available for download :)
Thanks again Ruth. Keep up the great work!
Ruth is the Truth.
🤔....
With capital T? ;)
/Ruth
Yes, you are most deserving of the capitalized "T".
Thank you :)
Overjoyed that you find my videos so useful!
/Ruth
Hi Ruth , as you said it does not contain measure but you used measure "no employees" , is this measure or column, calculator sign shows it is measure. My other question is that how can I register myself for Power BI community. I have Power BI installed but I cant register they said you need admin right to register it. Is there any other way to register in power bi community.
Hi Adnan,
Hope this helps:
community.powerbi.com/t5/Community-Accounts-Registration/How-to-Register-for-a-Power-BI-Community-Account/ta-p/680253
/Ruth
Hello, great class, I'd like to know if you can filter a sales table that shows which customers did not get sales on certain dates by selecting the months on the sliders to show on the map that those customers should be sought to improve the company's performance. We can also put a segmenter to select by type of product which did not buy would be a great analysis. Do you have something similar that can show us? Thank you in advance!
Hi Alessandro,
Yes, that can be done. Share some sample data and model in the power bi community to get detailed help. I don’t have a video about it yet.
/Ruth
@@CurbalEN
send me the link please
Community.powerbi.com
/Ruth
@@CurbalEN infelizmente essa comunidade não responde nada fica difícil de ajudar as pessoas.
Mais obrigado!
Sorry to hear that, I get too many requests to help everybody. I have a video on that topic on Wednesday (getting help), hope it will help you get proper help.
/Ruth
Hi, I am writing the same Dax in visual studio, making a Tabular model cube, but I am getting the error. Servers:=CALCULATE([CompTCt],FILTER(ADComputerInfo,ADComputerInfo[ComputerType] ="Servers"||ADComputerInfo, ADComputerInfo[ComputerType] ="DomainControllers"))
Please help !!!!