CALCULATE in DAX #07: Add multicolumn filters
Вставка
- Опубліковано 10 лют 2025
- Learn how to use complex filters on multiple columns without using expensive table filters.
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
CALCULATE function: dax.guide/calc...
This is great!!! I am new to DAX and spent half a day trying a very basic measurement but not getting the result I wanted until today when I found this video, amazing.
I am a real fan of your work. The simplicity with which they explain the ‘problems’ presented is really impressive.
Amazing
Amazing. So many concerns are alleviated when you understand the simple fact of FILTER being a table, rather than a QDR.
Excellent explanation, very clear as always!
For those who are new to DAX, you rather use REMOVEFILTERS function instead of ALL. They do the same job, but REMOVEFILTERS makes your code more readable
How is REMOVEFILTERS more readable than ALL?
ALL can be used both as a table expression of as filter modifier: in the latter case it removes a filter, it does not return a table.
REMOVEFILTERS can be used only as a filter modifier, removing a filter. For this reason, it is considered more readable, because its behavior must not be interpreted based on the position within the arguments of the external function.
Ah, finally understanding how to use KEEPFILTERS
You should also checkout this article by the super team. I permanently changed the way I write these types of filters after reading it.
www.sqlbi.com/articles/using-keepfilters-in-dax/
Simple, clear explanation! I like it. Congratulations!
Great explanation sir! Iam a novice in Dax. I Learnt something new today.
Thanks for such great content
You made it as simple as possible once again👍. Hopefully next will be calculate with more table filters ..
era fantastico, veramente , grazie mille
It more clear to me then reading the book DAX translated into Russian
Hi, thank you for all the help. I’m new in dax and have the following problem: 3 tables, all with a column containing names. Considering that not all names are in the 3 tables, which function should I use to combine the names from the 3 tables in one column in a new table? Thank you so much
Sir, very nice explanation
Great Explanation !!
Thank you so much for these amazingly wonderful lessons. I just need to know what could I use instead of the measure you used in the calculate function? I need to create a calculated column that returns the output of applying filters on two columns in the same table where the new table classifies the input of the two filtered columns into two groups using either switch or if. Could you please explain it to me?
Great stuff!!
No idea what happened at the end, we had outer filter on color so let's say we're inazure context, and we say keepfilters (So we require still azure), and then we combine it with Black + Contoso... then how do we obtain any value if one product can't be both azure and black?
Something has changed since this video was recorded.
Now the expression
CALCULATE(
[measure],
= "abc" || = "def"
)
is accepted.
Yes - now you can do that in Power BI only when and belong to the same table. Older versions of DAX (Power Pivot, SSAS) don't have this feature.
i'm trying to calculate a formula that sum sales filtered from two criteria from a column and three criteria from another column. all the columns are in the same table. your formula doesn't give me back the result attempted. i'm not convinced about using || but AND because all the criteria are accepted. Could you help me?
Super, Thank You Sir
Please can I use calculate and MAX functions? I may have already summed my sales but I want it to give me the product with the maximum sales. Any help, please?
Thanks Alberto
Grazie !
Hey there,
Can't we use ALLSELECTED here as it will remove the row context filter but keep the outside filter from slicers
Thank you... i have a question pls.... how to multiply 2 measures (not 2 columns) and then get their total.
Correct me if I am wrong, but following March 2021 version of PowerBI, the " || " double pipe operator can be used as an "OR" statement, inside of FILTER.
Correct - the video was recorded before that date, and the new syntax is still not available in Power Pivot and Analysis Services 2019.
I suppose if the outer filter is country, then select a country, the table will show the sales of Contoso and Green, and when changing countries, the sales amount will change accordingly. but if using ALL, changing countries will not display different sales amount... do I get the point here??
I guess this guy has written DAX.. whenever I think aha, I am gettng it, he smashes with another function
io comprato il libro, e grande libro
Thanks. Just wanted to understand instead of using All and replacing the filters from product table why cant we use only table name i.e. product without using All?? Will it not work??
The semantic would be different. Read this article for more details: www.sqlbi.com/articles/filter-arguments-in-calculate/
Dear Team
I have tried to use || operator and the OR function as well. I have come across an observation that both of these options exclude the records where Both of the Conditions are true i.e. Color is Green and Brand is Contoso. I came to know this when I added the Total Sales of Contoso and Total Sales of Green. There is a difference between (1) total amount of Sales of Contoso + Total amount of Green and (2) Total amount of sales derived based on measure created for Brand = Contoso OR Color = Green. The difference is exactly the amount of Amount of Sales derived from the measure created for Brand = Contoso AND Color = Green. Kindly guide if the OR / || operator works like the exclusivity check option in Microsoft Excel's OR / XOR function. Kindly guide.
I noticed this. It is because of the application of Brand and Color filter at the same time. Also, after applying KEEPFILTERS as shown the condition changes to Contoso & the Color in the visual except the Total Level. Trying to find a solution only to display Contoso or Green.
Hello, please share the source where we can pratice the videos content. Thanks
We always use the same Contoso model you can find in many articles on www.sqlbi.com
@@SQLBI tks, Please exactly the link to download the Contoso Database
I feel like if we use ALLSELECTED, the result would be indentical? am i correct?
The result could be identical in particular conditions but in general the semantic is different so you could obtain different results, depending on the existing filter context.
Hi Alberto, I agree to Rachim. To my understanding I do not see any scenario where the code would produce different results. Could you draw one?
Thank you so much for the video. Would it be possible to share the pbix file ?It would be helpful for us to do some trail and error after looking at this video
We use the same model we also have in most of the articles we publish on SQLBI.
For example, you can download the sample file of this article: www.sqlbi.com/articles/understanding-the-difference-between-lastdate-and-max-in-dax/
Can you please tell me if summarize addcolums table work with direct query? Will it update automatically without refreshing? Is it better to use groupby function?
GROUPBY should be used only in particular cases, read www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/
SUMMARIZE works in DirectQuery. Automatic updates - it depends, if you use DirectQuery the report is updated when you refresh the report, but this still requires to query the data source.
I have tested this formule and it seem's working :
GREENMODEL_TEST =
CALCULATE(
sum(FactInternetSales[SalesAmount]),
KEEPFILTERS( DimProduct[ModelName]="Sport-100" || DimProduct[color]="BLACK" )
Hi Alberto , i don't know but i feel like I don't understand any things about DAX
because i was thinking of using allselected in place of keepfilters can you give me clarification please
Start with the row context and filter context before approaching CALCULATE. We have a free 2-hours video course here: www.sqlbi.com/p/introducing-dax-video-course/
What is the best approach to do OR on columns from 2 different dimension table? Summarize over fact or crossjoin?
See this: www.sqlbi.com/articles/using-or-conditions-between-slicers-in-dax/
What if we have the filter columns from two different dimension tables? Can we combine two columns of different tables in the same manner: ALL( Product[Brand] , Customer[Country] )? Say we want to see sales of Contoso and/or sales in the USA.
No, you have to use: CROSSJOIN ( ALL ( Product[Brand] ), ALL ( Customer[Country] ) )
@@SQLBI Thank you! Alternatively, as you teach, there is this ADDCOLUMNS and SUMMERIZE tandem. I hoped perhaps there was a new shorter way:-)
Would calculate work if it's text rather them value?
yes.
Contoso or green sales = CALCULATE(
[Sales Amount],
'Product'[Brand]="Contoso" || 'Product'[Color]="Green")
It is working in my power bi.
did I miss anything ?
It changed after we published the video, we should update it!
Great. However, which works better - KEEPFILTERS or ALLSELECTED ?
It depends, they are different!
1. USA B Sales = CALCULATE(
[Total Sales)],
Logic[Country]="USA" || Logic[Product]="B"
).
It is Working in my Power BI Desktop.
2. What is meant by the True/False expression inside CALCULATE?
3. What is Compact Syntax && Extended Syntax.
Is it possible to do same thing when data is coming from two different Source
Yes.
@@SQLBI can you please let me know. How to figure it out. If you created any DAX expression. Any video you have been created, please let me knwo
@@SQLBIhello any update. See the catch is here that I am using two data source and ALL condition works only in one column. Do you have any idea how can I approach for solution
You might want to read this article: www.sqlbi.com/articles/filter-arguments-in-calculate/
Could we have written the code without using All function inside the filter function, then keepfilter function would not have needed. Am I getting anything wrong here?
Yes, but the performance would be worst than with KEEPFILTERS.
@@SQLBI Thanks a lot for the reply. Any article to understand the reason behind this?
Not a specific article, it's something you see by comparing query plans. You can see more details about that looking for videos about DAX Studio and in the Optimizing DAX video course: www.sqlbi.com/p/optimizing-dax-video-course/
Can we use ALLSELECTED instead of KEEPFILTERS?
They have a very different meaning.
how to filter with not only static "smth"?
although I get it, it is still seems pretty abstract using only words
Hi All,
What if we want to use column from different tables with OR condition,
Eg:
TotalSales(Babywear or 2015 ) =
CALCULATE (
[Total Sales],
FILTER ( All(Category[CategoryName],OrderHeader[OrderYear]),
Category[CategoryName] = "Babywear" || OrderHeader[OrderYear] = 2015 )
)
The problem is with the All function here which cannot be used with columns from multiple tables, any work around for this
Use CROSSJOIN, see www.sqlbi.com/articles/specifying-multiple-filter-conditions-in-calculate/
Is it correct to make it as:
Calculate(something,
Filter((ALL(Product[Color]), product[color] = "green") || Filter((ALL(Product[Brand]), product[brand] = "Contos")) ?