Learn the difference between using VALUES and KEEPFILTERS in CALCULATE. How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt The definitive guide to DAX: www.sqlbi.com/books/the-defin... CALCULATE function: dax.guide/calculate/?aff=yt
That was ... simply put AWESOME! - I personally was wondering how to do something like that , and quite struggled to grasp filters precedence (if any). This is something I'll be implementing on my calculations for sure. Thanks for these great videos.
Excelente Video Alberto; de esta manera en segundo argumento de calculate "values" actúa como argumento de filtro y "removefilters" actúa como un modificador de la estructura original del contexto de filtro. Saludos desde Argentina
Hi! Amazing lecture! Unfortunately, I'm doing step-by-step your process but, even in the first step (once you remove filters) I get 100% for each row instead the % per the grand Total. Any idea, why this is hapenning? Thanks in advance!
Cool stuff. Thanks for that Mr Russo. Did you create your 'Sales' home table from using the "Enter data" method (remove the dummy col). Or is there a better method these days ?
Very nice video. I am actually facing issue with removefilter. It was working just fine, after I close the file and reopen it is not working. It won't ignore the filter, it was working just fine in another file with the same dax. Any idea what could be causing it ?
Hi Alberto, Amazing video! This is advance stuff, but I am learning. if you dont add Values(date[year]), you can see that the total is 100 procent. This is not correct because adding all the percentages would not lead to 100 procent. How would you tackle this if you want to show the correct total percentage using remove filters only.
Excellent. Very good alternative approach. I think we can still get the expected result with the combination of all and keep filters. what do you think?
The goal of the video is to explain the semantic difference of the two approaches. However, if you include ALL, it remove the filters so KEEPFILTERS will not have any filter to keep. Check out the order of evaluation of CALCULATE arguments: dax.guide/calculate/
It would be ideal to use VALUES ( 'Date'[YearQuarter] ) like in the demo, if you have a column with just Q1/Q2/Q3/Q4, you don't have a difference between different years and including both VALUES (one for year, one for quarter) might include wrong combinations in complex reports.
Please help me understand. If the filters over the date column have been removed why is the sales displaying individual percentages for each row in the matrix visual? Please anyone
If you apply a filter over Date[Date] *AND* the Date table is marked as a Date table, a REMOVEFILTERS ( Date ) is applied automatically, otherwise you have to manually remove the filters from the Date columns where you have a filter, if you want to do that.
Yes Agree. Thank you. Is there any DAX function that compare two tables? Scenario: Var tbl1 = calculate table( previous month filter applied) Var tbl1 = calculate table (current month filter applied) Goal is to compare the change in active status and other parameters from previous month to current month. Any suggestion?
It depends on many detail - you could use NATURALINNERJOIN or similar functions, and perform the comparison - but you have to carefully manage the data lineage so if you are not comfortable with these concepts, you should rely on easier approaches such as using FILTER functions iterating the table to compare.
@@SQLBI , yes I will try with natural inner join. I had used it in different scenario, during then it worked perfectly , wasn't sure for this scenario as I had join criteria on more than one column. Enjoying DAX with SQLBI. 👍
In a scenario when all the measures are non additive ( aggregation type = none) is there a way to override filter context of current date to be a date from previous quarter or year? But with all other existing filters applied?
The approach for overriding the Date column is the same. The difference for a non-additive measure is what you want to do toward other filters - but this is specific to the data model, cannot be described in a generic way.
i am not able to understand , why should we use values function here, when you add values function automatically percentage also changed, what is the importance of values function here. values function single column of unique values.
hi I am using Direct Query mode for almost 1 billion rows , I want to apply filter on a column location here but when i create a measure i need to ignore filter. I have used ALL function like CALCULATE(SUM(Sales[Amount], ALL(Sales[Location])) but it is taking 30-40 seconds. Is there any way i can optimize this DAX?
You should look at the SQL query generated - can it be optimized? If yes, you have to figure out whether there is a DAX syntax that can generate a better query plan. If not, you have to optimize SQL. Or just import the data - there are models with a larger amount of rows imported.
Yeah... wouldn't allexcept(dates, year) as a filter get you the same result? Or maybe filter(all(dates), Dates[year] = Max (dates[year]))? I usually go for these solutions.
Yes, but allexcept will not work when you use Date Hierarchy in a table visual. When you use 2 separated columns like Year, Month it works, but with hierarchy it calculates for all time. The one with values works in both cases.
I never meet tool which is so counterintitiive and complex to get simple results. Every basic and simple math calculations takes hours to figure out a solution. I really hate dax. Values gives a unique years table so how can data be filtered by all years if all are present?
You can use the ALL function, see this article: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ In general (because you said you don't like DAX), read this: qr.ae/pvfjDc You also may want to watch this: www.sqlbi.com/tv/dax-by-examples/
KEEPFILTERS is mentioned in the video, the syntax is not shown because it wouldn't be helpful for the purpose of the calculation. The video explains how to use VALUES instead of KEEPFILTERS assuming you already know how to use KEEPFILTERS. You can see a video specific for KEEPFILTERS here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Cj6rI0fRCaQ.html
