CALCULATE in DAX #09: VALUES vs. KEEPFILTERS

  Рет қаралды 63,953

SQLBI

SQLBI

Күн бұрын

Learn the difference between using VALUES and KEEPFILTERS in CALCULATE.
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
CALCULATE function: dax.guide/calc...

Пікірлер: 78
@dawnkernen3961
@dawnkernen3961 3 жыл бұрын
Four days of my life gone. 4 minutes with Alberto - solved. Thank you!
@ikar2k
@ikar2k Жыл бұрын
You're happy man. I've lost four years to catch how it's works 😅 DAX is enjoying))
@Jam-Ryu88
@Jam-Ryu88 3 жыл бұрын
3 days i've been stuck! you're a genius, thank you!
@tangtom2478
@tangtom2478 3 жыл бұрын
This is so advanced, I never figured out how to use values, now finally understand a bit! Thank you!
@avirupchanda5450
@avirupchanda5450 11 күн бұрын
God of dax ♥️🔥
@mrgreenteatime2987
@mrgreenteatime2987 3 жыл бұрын
I found dax to be very powerful, but also quite unintuitive language to grasp. Thank you for the video
@aburnett04
@aburnett04 3 жыл бұрын
Values has been one of the more challenging DAX functions to understand. Thanks for explaining it !!
@MrSammy1188
@MrSammy1188 3 жыл бұрын
Legend, got stuck with this issue today, luckily remembered this video
@danielsiffredi8403
@danielsiffredi8403 3 жыл бұрын
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.
@LSiebra
@LSiebra 8 ай бұрын
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?
@hillarymushi2227
@hillarymushi2227 5 ай бұрын
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
@juanpablorvvv
@juanpablorvvv 9 ай бұрын
Why VALUES instead of CALENDAR[Year]=MAX(CALENDAR[Year])?
@stephanweaver1960
@stephanweaver1960 3 жыл бұрын
I would explain it very very differently myself - but thanks.
@zxccxz164
@zxccxz164 7 ай бұрын
how can year not be in filter context? If you have a calendar table q1-2007 would be in year 2007
@Kmsbi
@Kmsbi 3 жыл бұрын
You are awesome Alberto. Thanks for sharing wonderful knowledge.
@szpyrkowski
@szpyrkowski 3 жыл бұрын
Alberto, I think I'm in love.. wtih DAX
@Wzxxx
@Wzxxx Жыл бұрын
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?
@SQLBI
@SQLBI Жыл бұрын
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/
@김차수-k2u
@김차수-k2u 3 жыл бұрын
this is very helpful for me to understand a applying values fuction in calculate exactly, i have been always confused "Values".
@salmanj786
@salmanj786 2 жыл бұрын
Could we have replaced VALUES with DISTINCT here? Can we always use DISTINCT and never use VALUES?
@SQLBI
@SQLBI 2 жыл бұрын
It depends, they are not the same. See www.sqlbi.com/articles/blank-row-in-dax/
@leonardobrondani924
@leonardobrondani924 3 жыл бұрын
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
@kebincui
@kebincui 2 жыл бұрын
Thanks Alberto 👍👍🌹🌹
@sass520
@sass520 Жыл бұрын
Can someone help me to understand the value part?
@jameszhou162
@jameszhou162 3 жыл бұрын
Thank you so much Master Ferrari
@judeLondon
@judeLondon 3 жыл бұрын
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 ?
@feucht38
@feucht38 3 жыл бұрын
Mr Ferrari I believe :)
@莫迪-z4w
@莫迪-z4w 3 жыл бұрын
Thank you so much for the videos. Informative, easy to understand. Big big fan from China !
@ajithkumar1870
@ajithkumar1870 3 жыл бұрын
Is power bi used in China at all?
@aspacsa1
@aspacsa1 3 жыл бұрын
Excellent and simple explanation!!!!
@KR-good
@KR-good 3 жыл бұрын
I love DAX so much.
@jaikumardaiya4503
@jaikumardaiya4503 3 жыл бұрын
You are the real boss of DAX
@sravankumar1767
@sravankumar1767 3 жыл бұрын
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.
@oybekdavronov5638
@oybekdavronov5638 3 жыл бұрын
i think allexcept gives the same result as it is , good to know
@wojtekmzks
@wojtekmzks 3 жыл бұрын
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.
@arnaumartin6700
@arnaumartin6700 3 жыл бұрын
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!
@MentalCycle
@MentalCycle 2 жыл бұрын
Putting KEEPFILTERS into the name and not even mentioning it in the video 👎
@SQLBI
@SQLBI 2 жыл бұрын
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: kzbin.info/www/bejne/eZuZo3xmm7d2l7M
@deepakbhattacharya6363
@deepakbhattacharya6363 3 жыл бұрын
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 ?
@supersayan9888
@supersayan9888 3 жыл бұрын
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.
@danjarupath
@danjarupath 3 жыл бұрын
I love your vdo
@sayananalytics3445
@sayananalytics3445 3 жыл бұрын
It is really disgusting to see 3 dislikes..Just want to see their faces..
@HugoVenturini
@HugoVenturini 3 жыл бұрын
Bravo, Alberto!!!!!
@Unbox747
@Unbox747 3 жыл бұрын
Thank you!
@yeraldo05
@yeraldo05 3 жыл бұрын
Mind-blowing!!!
@granand
@granand 2 жыл бұрын
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
@QasimAli-gy3nb
@QasimAli-gy3nb Жыл бұрын
Can you do a detailed video on this topic 4:23 Alberto, please
@danjarupath
@danjarupath 3 жыл бұрын
Ciao
@NL-tq1yr
@NL-tq1yr 2 жыл бұрын
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.
@SQLBI
@SQLBI 2 жыл бұрын
Read this article: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
@robertocardenosa493
@robertocardenosa493 3 жыл бұрын
In that VALUES argument, could you type ('Date') instead of ('Date'[Year])? Would it work the same?
@SQLBI
@SQLBI 3 жыл бұрын
No, it would be a different meaning (more columns and a different cardinality)
@elrevesyelderecho
@elrevesyelderecho 3 жыл бұрын
Thanks. So, if I want to have correct percentage within each Q - 2007, should I include another line with VALUES ( 'Date' [Quater] )?
@SQLBI
@SQLBI 3 жыл бұрын
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.
@noahhadro8213
@noahhadro8213 3 жыл бұрын
could I use "ALL" instead of "Remove filters".... what is the difference?
@SQLBI
@SQLBI 3 жыл бұрын
no differences, REMOVEFILTERS is more clear when used as a CALCULATE modifier whereas ALL can be used also a table function.
@NaraMeerammaTrust
@NaraMeerammaTrust 3 жыл бұрын
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?
@SQLBI
@SQLBI 3 жыл бұрын
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/
@souhasghaier7797
@souhasghaier7797 3 жыл бұрын
Thank you very much for this video! could you put a link to the report used in this video please ? Thanks.
@SQLBI
@SQLBI 3 жыл бұрын
This is the standard Contoso example we use in many other demos. Most of the articles on www.sqlbi.com use the same data.
@1yyymmmddd
@1yyymmmddd 3 жыл бұрын
Great video. If we were not using REMOVEFILTERS would CALCULATE respect Month - Year filter in the table before calculating sum of sales ?
@SQLBI
@SQLBI 3 жыл бұрын
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.
@1yyymmmddd
@1yyymmmddd 3 жыл бұрын
@@SQLBI Thanks!
@Bharath_PBI
@Bharath_PBI 3 жыл бұрын
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?
@SQLBI
@SQLBI 3 жыл бұрын
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.
@Bharath_PBI
@Bharath_PBI 3 жыл бұрын
@@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. 👍
@krdhuri
@krdhuri 3 жыл бұрын
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?
@SQLBI
@SQLBI 3 жыл бұрын
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.
@krdhuri
@krdhuri 3 жыл бұрын
@@SQLBI can you please suggest any optimized dax for this?
@SQLBI
@SQLBI 3 жыл бұрын
No - read the previous answer - thanks!
@sid5201
@sid5201 3 жыл бұрын
ALLEXCEPT
@singhrakeshr
@singhrakeshr 3 жыл бұрын
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?
@SQLBI
@SQLBI 3 жыл бұрын
Yes, read this article: www.sqlbi.com/articles/semi-additive-measures-in-dax/
@singhrakeshr
@singhrakeshr 3 жыл бұрын
@@SQLBI thanks but my measure is non additive - a percentage field pre calculated and stored in datasource table. So aggregation type is none
@SQLBI
@SQLBI 3 жыл бұрын
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.
@hyS-n5m
@hyS-n5m 3 жыл бұрын
dear teacher , is it like allexcept ?
@SQLBI
@SQLBI 3 жыл бұрын
No, there is a difference explained here: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
@mehdihammadi6145
@mehdihammadi6145 3 жыл бұрын
Great demonstration of combining removefilter and values
CALCULATE in DAX #10: Using TREATAS
4:18
SQLBI
Рет қаралды 50 М.
Using ALLEXCEPT vs ALL VALUES
13:35
SQLBI
Рет қаралды 55 М.
Best practices for using KEEPFILTERS in DAX
28:24
SQLBI
Рет қаралды 11 М.
Row context in DAX explained visually
21:45
SQLBI
Рет қаралды 15 М.
Filter context in DAX explained visually
24:40
SQLBI
Рет қаралды 24 М.
When to use KEEPFILTERS over iterators
18:56
SQLBI
Рет қаралды 48 М.
DAX ALLSELECTED Function
17:35
Goodly
Рет қаралды 14 М.
DAX FILTER Function
8:42
Goodly
Рет қаралды 28 М.
Create insightful calculations using CONCATENATEX.
12:27
Goodly
Рет қаралды 24 М.
All vs Allselected vs Allexcept DAX | Power BI Interview
12:25
LearnWidGiggs
Рет қаралды 24 М.