CALCULATE in DAX #09: VALUES vs. KEEPFILTERS
Vložit
- čas přidán 4. 11. 2020
- 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 - Věda a technologie
Four days of my life gone. 4 minutes with Alberto - solved. Thank you!
You're happy man. I've lost four years to catch how it's works 😅 DAX is enjoying))
This is so advanced, I never figured out how to use values, now finally understand a bit! Thank you!
3 days i've been stuck! you're a genius, thank you!
Values has been one of the more challenging DAX functions to understand. Thanks for explaining it !!
Legend, got stuck with this issue today, luckily remembered this video
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.
You are awesome Alberto. Thanks for sharing wonderful knowledge.
You are the real boss of DAX
Excellent and simple explanation!!!!
Thank you so much for the videos. Informative, easy to understand. Big big fan from China !
Is power bi used in China at all?
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
Bravo, Alberto!!!!!
Thank you so much Master Ferrari
Mind-blowing!!!
this is very helpful for me to understand a applying values fuction in calculate exactly,
i have been always confused "Values".
I found dax to be very powerful, but also quite unintuitive language to grasp.
Thank you for the video
I love DAX so much.
Thanks Alberto 👍👍🌹🌹
Thank you!
Great demonstration of combining removefilter and values
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 ?
Mr Ferrari I believe :)
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 ?
Sir, if i have to test this out, I understand this is Contasso table, so which tables do you use for these short videos please
I love your vdo
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.
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
Alberto, I think I'm in love.. wtih DAX
Thanks.
So, if I want to have correct percentage within each Q - 2007, should I include another line with VALUES ( 'Date' [Quater] )?
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.
Why when you use "RemoveFilters" and in the row context has the Quarter-Year, why all the numberrs aren't the same? Which filter is being applied?
Can you do a detailed video on this topic 4:23 Alberto, please
Thank you very much for this video! could you put a link to the report used in this video please ? Thanks.
This is the standard Contoso example we use in many other demos. Most of the articles on www.sqlbi.com use the same data.
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/
In that VALUES argument, could you type ('Date') instead of ('Date'[Year])? Would it work the same?
No, it would be a different meaning (more columns and a different cardinality)
Can someone help me to understand the value part?
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?
Yes, read this article:
www.sqlbi.com/articles/semi-additive-measures-in-dax/
@@SQLBI thanks but my measure is non additive - a percentage field pre calculated and stored in datasource table. So aggregation type is none
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.
how can year not be in filter context? If you have a calendar table q1-2007 would be in year 2007
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.
@@SQLBI can you please suggest any optimized dax for this?
No - read the previous answer - thanks!
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. 👍
Great video. If we were not using REMOVEFILTERS would CALCULATE respect Month - Year filter in the table before calculating sum of sales ?
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.
@@SQLBI Thanks!
dear teacher ， is it like allexcept ？
No, there is a difference explained here: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
could I use "ALL" instead of "Remove filters".... what is the difference?
no differences, REMOVEFILTERS is more clear when used as a CALCULATE modifier whereas ALL can be used also a table function.
Why VALUES instead of CALENDAR[Year]=MAX(CALENDAR[Year])?
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.
Read this article: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
i think allexcept gives the same result as it is , good to know
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.
Could we have replaced VALUES with DISTINCT here?
Can we always use DISTINCT and never use VALUES?
It depends, they are not the same. See www.sqlbi.com/articles/blank-row-in-dax/
I would explain it very very differently myself - but thanks.
It is really disgusting to see 3 dislikes..Just want to see their faces..
ALLEXCEPT
Ciao
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/
Putting KEEPFILTERS into the name and not even mentioning it in the video 👎
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: czcams.com/video/Cj6rI0fRCaQ/video.html