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))

This is so advanced, I never figured out how to use values, now finally understand a bit! Thank you!

Values has been one of the more challenging DAX functions to understand. Thanks for explaining it !!

3 days i've been stuck! you're a genius, thank you!

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.

Legend, got stuck with this issue today, luckily remembered this video

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!!!!!

Excellent and simple explanation!!!!

You are the real boss of DAX

this is very helpful for me to understand a applying values fuction in calculate exactly, i have been always confused "Values".

Thank you so much for the videos. Informative, easy to understand. Big big fan from China !

@ajithkumar1870

3 жыл бұрын

Is power bi used in China at all?

Mind-blowing!!!

Thank you so much Master Ferrari

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

I love your vdo

Alberto, I think I'm in love.. wtih DAX

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! 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 ?

@feucht38

3 жыл бұрын

Mr Ferrari I believe :)

Thank you very much for this video! could you put a link to the report used in this video please ? Thanks.

@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.

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.

Can you do a detailed video on this topic 4:23 Alberto, please

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

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/

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

Thanks. So, if I want to have correct percentage within each Q - 2007, should I include another line with VALUES ( 'Date' [Quater] )?

@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.

Great video. If we were not using REMOVEFILTERS would CALCULATE respect Month - Year filter in the table before calculating sum of sales ?

@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

3 жыл бұрын

@@SQLBI Thanks!

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

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

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

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. 👍

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 someone help me to understand the value part?

i think allexcept gives the same result as it is , good to know

@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.

In that VALUES argument, could you type ('Date') instead of ('Date'[Year])? Would it work the same?

@SQLBI

3 жыл бұрын

No, it would be a different meaning (more columns and a different cardinality)

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.

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

3 жыл бұрын

Yes, read this article: www.sqlbi.com/articles/semi-additive-measures-in-dax/

@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

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.

how can year not be in filter context? If you have a calendar table q1-2007 would be in year 2007

could I use "ALL" instead of "Remove filters".... what is the difference?

@SQLBI

3 жыл бұрын

no differences, REMOVEFILTERS is more clear when used as a CALCULATE modifier whereas ALL can be used also a table function.

dear teacher ， is it like allexcept ？

@SQLBI

3 жыл бұрын

No, there is a difference explained here: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

I would explain it very very differently myself - but thanks.

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

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

3 жыл бұрын

@@SQLBI can you please suggest any optimized dax for this?

@SQLBI

3 жыл бұрын

No - read the previous answer - thanks!

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

2 жыл бұрын

Read this article: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

Why VALUES instead of CALENDAR[Year]=MAX(CALENDAR[Year])?

Could we have replaced VALUES with DISTINCT here? Can we always use DISTINCT and never use VALUES?

@SQLBI

Жыл бұрын

It depends, they are not the same. See www.sqlbi.com/articles/blank-row-in-dax/

ALLEXCEPT

Ciao

It is really disgusting to see 3 dislikes..Just want to see their faces..

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/

Putting KEEPFILTERS into the name and not even mentioning it in the video 👎

@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: kzread.info/dash/bejne/dZ5q1KtplrWpkbQ.html