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
@dawnkernen39613 жыл бұрын
Four days of my life gone. 4 minutes with Alberto - solved. Thank you!
@ikar2k Жыл бұрын
You're happy man. I've lost four years to catch how it's works 😅 DAX is enjoying))
@Jam-Ryu883 жыл бұрын
3 days i've been stuck! you're a genius, thank you!
@tangtom24783 жыл бұрын
This is so advanced, I never figured out how to use values, now finally understand a bit! Thank you!
@avirupchanda545011 күн бұрын
God of dax ♥️🔥
@mrgreenteatime29873 жыл бұрын
I found dax to be very powerful, but also quite unintuitive language to grasp. Thank you for the video
@aburnett043 жыл бұрын
Values has been one of the more challenging DAX functions to understand. Thanks for explaining it !!
@MrSammy11883 жыл бұрын
Legend, got stuck with this issue today, luckily remembered this video
@danielsiffredi84033 жыл бұрын
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.
@LSiebra8 ай бұрын
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?
@hillarymushi22275 ай бұрын
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
@juanpablorvvv9 ай бұрын
Why VALUES instead of CALENDAR[Year]=MAX(CALENDAR[Year])?
@stephanweaver19603 жыл бұрын
I would explain it very very differently myself - but thanks.
@zxccxz1647 ай бұрын
how can year not be in filter context? If you have a calendar table q1-2007 would be in year 2007
@Kmsbi3 жыл бұрын
You are awesome Alberto. Thanks for sharing wonderful knowledge.
@szpyrkowski3 жыл бұрын
Alberto, I think I'm in love.. wtih DAX
@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 Жыл бұрын
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/
@김차수-k2u3 жыл бұрын
this is very helpful for me to understand a applying values fuction in calculate exactly, i have been always confused "Values".
@salmanj7862 жыл бұрын
Could we have replaced VALUES with DISTINCT here? Can we always use DISTINCT and never use VALUES?
@SQLBI2 жыл бұрын
It depends, they are not the same. See www.sqlbi.com/articles/blank-row-in-dax/
@leonardobrondani9243 жыл бұрын
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
@kebincui2 жыл бұрын
Thanks Alberto 👍👍🌹🌹
@sass520 Жыл бұрын
Can someone help me to understand the value part?
@jameszhou1623 жыл бұрын
Thank you so much Master Ferrari
@judeLondon3 жыл бұрын
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 ?
@feucht383 жыл бұрын
Mr Ferrari I believe :)
@莫迪-z4w3 жыл бұрын
Thank you so much for the videos. Informative, easy to understand. Big big fan from China !
@ajithkumar18703 жыл бұрын
Is power bi used in China at all?
@aspacsa13 жыл бұрын
Excellent and simple explanation!!!!
@KR-good3 жыл бұрын
I love DAX so much.
@jaikumardaiya45033 жыл бұрын
You are the real boss of DAX
@sravankumar17673 жыл бұрын
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.
@oybekdavronov56383 жыл бұрын
i think allexcept gives the same result as it is , good to know
@wojtekmzks3 жыл бұрын
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.
@arnaumartin67003 жыл бұрын
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!
@MentalCycle2 жыл бұрын
Putting KEEPFILTERS into the name and not even mentioning it in the video 👎
@SQLBI2 жыл бұрын
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
@deepakbhattacharya63633 жыл бұрын
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 ?
@supersayan98883 жыл бұрын
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.
@danjarupath3 жыл бұрын
I love your vdo
@sayananalytics34453 жыл бұрын
It is really disgusting to see 3 dislikes..Just want to see their faces..
@HugoVenturini3 жыл бұрын
Bravo, Alberto!!!!!
@Unbox7473 жыл бұрын
Thank you!
@yeraldo053 жыл бұрын
Mind-blowing!!!
@granand2 жыл бұрын
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 Жыл бұрын
Can you do a detailed video on this topic 4:23 Alberto, please
@danjarupath3 жыл бұрын
Ciao
@NL-tq1yr2 жыл бұрын
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.
@SQLBI2 жыл бұрын
Read this article: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
@robertocardenosa4933 жыл бұрын
In that VALUES argument, could you type ('Date') instead of ('Date'[Year])? Would it work the same?
@SQLBI3 жыл бұрын
No, it would be a different meaning (more columns and a different cardinality)
@elrevesyelderecho3 жыл бұрын
Thanks. So, if I want to have correct percentage within each Q - 2007, should I include another line with VALUES ( 'Date' [Quater] )?
@SQLBI3 жыл бұрын
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.
@noahhadro82133 жыл бұрын
could I use "ALL" instead of "Remove filters".... what is the difference?
@SQLBI3 жыл бұрын
no differences, REMOVEFILTERS is more clear when used as a CALCULATE modifier whereas ALL can be used also a table function.
@NaraMeerammaTrust3 жыл бұрын
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?
@SQLBI3 жыл бұрын
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/
@souhasghaier77973 жыл бұрын
Thank you very much for this video! could you put a link to the report used in this video please ? Thanks.
@SQLBI3 жыл бұрын
This is the standard Contoso example we use in many other demos. Most of the articles on www.sqlbi.com use the same data.
@1yyymmmddd3 жыл бұрын
Great video. If we were not using REMOVEFILTERS would CALCULATE respect Month - Year filter in the table before calculating sum of sales ?
@SQLBI3 жыл бұрын
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.
@1yyymmmddd3 жыл бұрын
@@SQLBI Thanks!
@Bharath_PBI3 жыл бұрын
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?
@SQLBI3 жыл бұрын
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_PBI3 жыл бұрын
@@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. 👍
@krdhuri3 жыл бұрын
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?
@SQLBI3 жыл бұрын
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.
@krdhuri3 жыл бұрын
@@SQLBI can you please suggest any optimized dax for this?
@SQLBI3 жыл бұрын
No - read the previous answer - thanks!
@sid52013 жыл бұрын
ALLEXCEPT
@singhrakeshr3 жыл бұрын
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?
@SQLBI3 жыл бұрын
Yes, read this article: www.sqlbi.com/articles/semi-additive-measures-in-dax/
@singhrakeshr3 жыл бұрын
@@SQLBI thanks but my measure is non additive - a percentage field pre calculated and stored in datasource table. So aggregation type is none
@SQLBI3 жыл бұрын
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-n5m3 жыл бұрын
dear teacher , is it like allexcept ?
@SQLBI3 жыл бұрын
No, there is a difference explained here: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
@mehdihammadi61453 жыл бұрын
Great demonstration of combining removefilter and values