Machine Learning

Under Hood: How Dax works with filters

nimda 18 hours ago

0 0 5 minutes read

Let's start with a simple table:

Figure 1 – a simple start table with (drawing by the writer)

Each row in the matrix visual shows complete Internet sales per month.
So far, it is very good.

Translation that we see the complete online sale that is being made a month.

But this is the whole truth.

Let's look at the data model:

Figure 2 – Part of the data model on the Date table and the true table (image by the writer)

If you look carefully, you can see that the relationship is built between the two columns of two.

There is no relationship with the month column.

When we take this method, the above interpretation cannot completely identify.

Perfect Translation should be: Each line shows the amount of online sales. Dark table lines are organized by months. Each row shows the complete sale of every month.

It is a step close to understanding the Dax typically, and the time of Foren Telligence especially, when we see this information.

Let's go on one step.

YTD and the basic question

Now, let's add a YTD assessment ratio what happens:

Figure 3 – YTD rating and consequences are in the same table before (character by the author)

The average is nothing special, and the results are easy to understand.

Now, let's look at the specific work () the work you do.

Description from Dax.Guide: “Returns a set of days Annually to the last time in the visual context “.

What does this exactly mean?

To enter this question, write the DAX question first, to get a list of days June 2024, as done in the answer above:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    
EVALUATE
    SUMMARIZECOLUMNS('Date'[Date]
                        ,YearFilter
                        ,MonthFilter
                        )

The result is a 30-day list of June:

Figure 4 – Question question to get every day on June 2024 (an illustration by the writer)

This is a filter used in a row in June 2024 on the matrix shown above.

What is the result when using the work of days () for the result?

Here's the question:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    VAR BasisDates = CALCULATETABLE(
                            SUMMARIZECOLUMNS('Date'[Date]
                                        ,YearFilter
                                        ,MonthFilter
                                        )
                                    )
    
    VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
                                    )
                                    
EVALUATE
    YTDDates

Here's what, the result:

Figure 5 – List of days beginning of January until the last day of June 2024 (figure by the writer)

List of 182 lines, containing all the days starting from the beginning of the year until the last day of June 2024.

This is the meaning of YTD.

When we look at the following rate:

Online Sales (YTD) =
VAR YTDDates = DATESYTD('Date'[Date])

RETURN
    CALCULATE([Sum Online Sales]
                ,YTDDates
                )

We see that the variable YTDDates say “only” a list of days used as a filter to count () work.

This is the key to the duties of chestsy.

Go back a year – some examples

What happens when using another job results?

Example, SAMEPOIREOOIREENEEREQUAR ()?

To answer this question, I use the next DAX question:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    VAR BasisDates = CALCULATETABLE(
                            SUMMARIZECOLUMNS('Date'[Date]
                                        ,YearFilter
                                        ,MonthFilter
                                        )
                                    )
    
    VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
                                    )
                                    
    VAR YTDDatesPY = SAMEPERIODLASTYEAR(YTDDates)
                                    
EVALUATE
    YTDDatesPY

I deliberately separate the LongePeriodioDoDe () Daily () from daily () to make it easier to learn. It was going to happen to Nest Datesytd () in AlforderedelTeeear ().

In this regard, we have 181 lines, as 2024 were LEAP year.
And days are answered back in one year:

Figure 6 – The result of the question after applying for sects

Therefore, too, when we spend time for Time Provintence performance, work, for example, dates () returns to the list of days.
Please note: when using a filter, any Filters available on the Date table is deleted.

Logic customized

Now, let us use this information on the custom Time Time Intime Logic.

First, let's change a bit of the Fil of Year and Month:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202406  }, 'Date'[MonthKey])
    
EVALUATE
    SUMMARIZECOLUMNS('Date'[Date]
                        , YearMonthFilter
                        )

The result of this question is similar to the beginning of this article.

In this case, I put the filter in the number of numbers in the [MonthKey] column.

How can I go back to the last year?

Considering mathematics, it's just about 100:

202406 – 100 = 202306

Let's try:

Figure 7 – The result of the question after 100 deduction from [MonthKey] Column (Illustration by the author)

You can also do this with other numerical formats.

When you take a financial year, for example, follows: 2425 (in the financial year 24/25)

You can get 101 to get the past financial year: 2425 – 101 = 2324

Another example of the customary time logic is an active measure, where each day, we calculate the average number of 10 days:

Figure 8 – Code and Default Measurement Date of the Past Ten Days (Based Volume)

As the content of the variable datanguange and date list, I can apply the same () its work, and find the result I need:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    // 1. Get the first and last Date for the current Filter Context
    VAR MaxDate = CALCULATE(MAX( 'Date'[Date] )
                            ,YearFilter
                            ,MonthFilter
                            )
    
    VAR MinDate =
        CALCULATE(
            DATEADD( 'Date'[Date], - 10, DAY )
            ,'Date'[Date] = MaxDate
            )
    
    // 2. Generate the Date range needed for the Moving average (Four months)
    VAR  DateRange =
     CALCULATETABLE(
        DATESBETWEEN( 'Date'[Date]
            ,MinDate
            ,MaxDate
        )
    )
    
EVALUATE
    SAMEPERIODLASTYEAR( DateRange )

And this is the result:

Figure 9 – The result of the moving quantity last year (writer's diagram)

This concept goes 11 lines, because it includes the last day of the month. In accordance with the required effect, we need to adjust the calculation of the first and last days of the Date List (filter used for an average).

Of course, this is a duplication of what I have shown above. However, it shows that the same way can be included in different cases.

As soon as you understand this, your intelligence work for intelligence and other activities accepted prices as the entry will be very easy to understand and know exactly.

Store

While using DAX Studio with questions, you can use the same questions in the DAX question tool inside Bi BIDKTTOP.

I deliberately used the questions to show that we work with tables all the time with Dax, even if we don't always be careful.

But important details we help our way to understand the Dax.

Although one of the Dax Code shown here can end up the advent of the advent of the Time Intellance Calendar for Time Intelligence Calendar. Jobs, such as Dateytd () or EamalOropalastlastyear (), is still located and acts in the same way too. In the meantime, nothing will change on the other hand, as explained by thoughts here and that are made.