What is the fastest measure?

[JeroenR]

Hi all,

I have a question about two measure that I have created, I would like to know which measure is faster and why? The reason of this is that I have a lot of memory troubles with the 'avergex' measure so I wanted to improve this. But the improved measure seems to use even more memory at first glance, mostly because of the extra lines of DAX. So I would like your opinion about it.

This is my newly created average measure: 

CountDays LY = 
SUMX (
    VALUES ( 'Date'[DWY] ),
    CALCULATE(
        DISTINCTCOUNT( 'count_measurement_feeds_dimensions'[datekey] ),
        FILTER ( ALL ( 'Date' ), 'Date'[DWY] = EARLIER( 'Date'[DWY] ) - 1 )
    )
)

AVG LYY = 
DIVIDE( [Total Footfall PY], [CountDays LY] )

This is my old average measure which takes a lot of memory:

AVG PY =
AVERAGEX( VALUES('Date'[Date]) , [Total Footfall PY])

You are probably wondering why I am not just counting the number of days in the selected period, because this measure is for calculating the average of the previous year, and it may be that in a period of 1 year ago there were fewer days than the currently selected period.

I would like to know your thoughts on this measure, if you have an even faster way of calculating the average of a previous period I would like to know as well, if possible with an explanation why.

[AlB · Accepted solution]

@JeroenR 

I thought the main issue was the system running out of memory? I was trying to get an equivalent that runs faster, without memory problems

I do not understand the role of 'count_measurement_feeds_dimensions'[datekey] in all this. You'll have to explain better, probably based on the logic behind what you do in your version of [CountDays LY]. And the relationships between the relevant tables in your model.

Try this (it assumes a relationship between DateT and 'count_measurement_feeds_dimensions')

AVG LYY V2 =
VAR filterT_ =
    SELECTCOLUMNS (
        ADDCOLUMNS ( DISTINCT ( DateT[DWY] ), "res", DateT[DWY] - 1 ),
        "DWY", [res]
    )
VAR filterDateT_ =
    CALCULATETABLE ( DateT, TREATAS ( filterT_, DateT[DWY] ), ALL ( DateT ) )
VAR TotalFootfallPY_ =
    CALCULATE ( [Total Footfall], filterDateT_ )
VAR CountDaysLY_ =
    COUNTROWS (
        CALCULATETABLE (
            DISTINCT ( 'count_measurement_feeds_dimensions'[datekey] ),
            TREATAS ( filterT_, DateT[DWY] ),
            ALL ( DateT )
        )
    )
RETURN
    DIVIDE ( TotalFootfallPY_, CountDaysLY_ )CountDays LY

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@JeroenR 

The crux here is determining the subset of days in the previous year Total Footfall will be applied on. You do this in a rather convoluted way and nesting filtering operations, which could very well be the cause of the memory problems. In filterT_ I am trying to build a one-column table that has that subset of relevant days in the previous year to then apply it as filter to Total Footfall (and count the number of days). And I am trying to do it in a way that I believe is simpler and will be less demanding in terms of resource consumption.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@JeroenR 

I thought the main issue was the system running out of memory? I was trying to get an equivalent that runs faster, without memory problems

I do not understand the role of 'count_measurement_feeds_dimensions'[datekey] in all this. You'll have to explain better, probably based on the logic behind what you do in your version of [CountDays LY]. And the relationships between the relevant tables in your model.

Try this (it assumes a relationship between DateT and 'count_measurement_feeds_dimensions')

AVG LYY V2 =
VAR filterT_ =
    SELECTCOLUMNS (
        ADDCOLUMNS ( DISTINCT ( DateT[DWY] ), "res", DateT[DWY] - 1 ),
        "DWY", [res]
    )
VAR filterDateT_ =
    CALCULATETABLE ( DateT, TREATAS ( filterT_, DateT[DWY] ), ALL ( DateT ) )
VAR TotalFootfallPY_ =
    CALCULATE ( [Total Footfall], filterDateT_ )
VAR CountDaysLY_ =
    COUNTROWS (
        CALCULATETABLE (
            DISTINCT ( 'count_measurement_feeds_dimensions'[datekey] ),
            TREATAS ( filterT_, DateT[DWY] ),
            ALL ( DateT )
        )
    )
RETURN
    DIVIDE ( TotalFootfallPY_, CountDaysLY_ )CountDays LY

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[JeroenR]

Hi @AlB ,

Yes the memory problem was caused by the AVERAGEX() function.

Thank you, this one works. Could you explain a little bit about the 'filterT_' variable that you have created?

I will try to look more into the metrics and see what this new measure is doing with the memory and speed of the report.

[AlB]

Hi @JeroenR 

Try this to start with. See if it's faster.

AVG LYY V2 =
VAR filterT_ =
    SELECTCOLUMNS (
        ADDCOLUMNS ( DISTINCT ( DateT[DWY] ), "res", DateT[DWY] - 1 ),
        "DWY", [res]
    )
VAR filterDateT_ =
    CALCULATETABLE ( DateT, TREATAS ( filterT_, DateT[DWY] ), ALL ( DateT ) )
VAR TotalFootfallPY_ =
    CALCULATE ( [Total Footfall], filterDateT_ )
VAR CountDaysLY_ =
    COUNTROWS ( filterDateT_ )
RETURN
    DIVIDE ( TotalFootfallPY_, CountDaysLY_ )

By the way, for the computation of the PY measure,  isn't the number of relevant days in the previous year exactly the same as the number of days int the current year?? Or can they be different due through this unusual requirement of omparing the dayname of this year vs previous year?

And something else important, since it could simplify things: Does                                                                                         'count_measurement_feeds_dimensions'[count_fw] 

have only one value per date (DWY) or several ones?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[JeroenR]

Hi, @AlB ,

Sorry for my late response, had a few bussy days. I have a small problem with the measure, because i can't use the date table for this one. I need to use the 'count_measurement_feeds_dimensions'[datekey] column.

Why? Because, just like you also asked, the number of relevant days in the previous year isn't always exactly the same as the number of days in the current year. That's the hardest thing about the measure, I need to calculate the amount of active days of the previous year based on the 'count_measurement_feeds_dimensions'[datekey] column.

And yes, the 'count_measurement_feeds_dimensions'[count_fw] column has multiple values per date.. 

That's why I used this measure, for counting the amount of days 1 year ago:

CountDays LY = 
SUMX (
    VALUES ( 'Date'[DWY] ),
    CALCULATE(
        DISTINCTCOUNT( 'count_measurement_feeds_dimensions'[datekey] ),
        FILTER ( ALL ( 'Date' ), 'Date'[DWY] = EARLIER( 'Date'[DWY] ) - 1 )
    )
)

My only question would be if there was a easier way to calculate these days. Because when I have this number I can easily calculate the average of the previous year.

[AlB]

Hi @JeroenR 

Where is the code for [Total Footfall PY] ? That will play an important role.

You can measure how fast they are with either the Performance Analyzer in PBI or in DAX Studio. It will depend on your model as well, not only on the code itself. Best if you can share the pbix

And it would also help if you explain what the measures do

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[JeroenR]

Hi @AlB, thanks for your reply. I will try to explain everything as good as possible.
This is the code of the [Total Footfall PY] measure:

Total Footfall PY = 
SUMX (
    VALUES ( 'Date'[DWY] ),
    CALCULATE (
        [Total Footfall],
        FILTER ( ALL ( 'Date' ), 'Date'[DWY] = EARLIER( 'Date'[DWY] ) - 1 )
    )
)

As you can seen, this code uses another measure named [Total Footfall]. The code of that measure is just a simple sum:

Total Footfall = 
SUM ( 'count_measurement_feeds_dimensions'[count_fw] )

I will try to explain what my intention of these measure are. My [Total Footfall] measure is a simple SUM measure on a integer column. I am using this measure for my Current Year calculation.

Then I have also a [Total Footfall PY] measure, this is a year on year comparison. This measure does exactly the same only calculates 1 year back. The 'DWY' column stands for "Day number in week * 1000000 + Weeknumber * 10000 + Year". This is also an integer column, I have got this column / measure from this page: https://community.powerbi.com/t5/Desktop/Weekday-Last-Year-Comparison/td-p/54561/page/2 

Why am I doing this? That is because I need to compare the dayname of this year vs previous year. For example 

05-12-2020 (saturday 5 dec '20) vs 07-12-2020 (saturday 7 dec '19).

Now my question about the average measure that I want to calculate. It could happen that in the previous year there are less days then in the current year. For example: I want to calculate the average of the whole November 2020 vs the whole November 2019, only in November 2019 I have 20 days of data and in November 2020 I have 30 days of data. That means that for the average of this year I have to use [Total Footfall] / 30, and for the previous year I have to use [Total Footfall PY] / 20. I can't use a simple 'distinctcount' on the date column, this works for the current year measure but not for the previous year measure.

So basically I need a measure that counts the days of 1 year back. Preferably one that counts the DWY column, because this one is the most accurate one.

I am using the [AVG Current Year] and [AVG Previous year] only in a line chart where I display the hours on the x-as. I hope everything has become a lot clearer, please if you need any more feedback let me know.