DAX Performance Issue

[TheStressMachine]

Hi,

I've taken the logic from this post (and other similar ones):
excel formula - DAX / PowerPivot query functions to spread aggregated values over time period - Stack Overflow

To create a pivot table with an even spread of revenue over time.

In my data table (CombinedOutput), I have 3 key fields:
StartDate
EndDate
RevenuePerDay (I created this in the source data trying to solve my performance problem, the post above does this calculation in DAX).

I also have a typical calendar table, unconnected per the post.

I'm trying to spread revenue per day across any slice of time (months, quarters, years, etc) and have two measures:

Measure1:
CALCULATE(SUM(CombinedOutput[RevPerDay]),
FILTER (
CombinedOutput,
CombinedOutput[StartDate] <= MAX ( Calendar[Date] )
&&CombinedOutput[EndDate] >= MAX (Calendar[Date] )
)
)

Measure2:
SUMX (
VALUES ( Calendar[Date] ),
SUMX ( VALUES (CombinedOutput ), [Measure1] )
)


This works, but I'm running into performance problems. The entire dataset takes about a minute to calculate and playing with various slicers to filter the data takes about 10 seconds a pop. My data table has about 7,000 rows and my calendar table has about 450. Adding Measure1 to the pivot doesn't cause a performance problem (but is obviously the wrong data), adding measure2 when measure1 doesn't filter against the calendar table isn't a performance problem either (but the spread goes on forever and ignores start/end). It seems that the double pass over the calendar table is causing the issues and I'm not a real data guy, just an example junkie, so I'm not sure if there is a more efficient way to achieve the same goal.

I would have to scramble the dataset before sharing so let me know if that's needed.

Any ideas on how to do this more efficiently? I would be a sad dude if I can't make this happen with a relatively small data set.

 

[jak82]

Hi,

I've taken the logic from this post (and other similar ones):
excel formula - DAX / PowerPivot query functions to spread aggregated values over time period - Stack Overflow

To create a pivot table with an even spread of revenue over time.

In my data table (CombinedOutput), I have 3 key fields:
StartDate
EndDate
RevenuePerDay (I created this in the source data trying to solve my performance problem, the post above does this calculation in DAX).

I also have a typical calendar table, unconnected per the post.

I'm trying to spread revenue per day across any slice of time (months, quarters, years, etc) and have two measures:

Measure1:
CALCULATE(SUM(CombinedOutput[RevPerDay]),
FILTER (
CombinedOutput,
CombinedOutput[StartDate] <= MAX ( Calendar[Date] )
&&CombinedOutput[EndDate] >= MAX (Calendar[Date] )
)
)

Measure2:
SUMX (
VALUES ( Calendar[Date] ),
SUMX ( VALUES (CombinedOutput ), [Measure1] )
)


This works, but I'm running into performance problems. The entire dataset takes about a minute to calculate and playing with various slicers to filter the data takes about 10 seconds a pop. My data table has about 7,000 rows and my calendar table has about 450. Adding Measure1 to the pivot doesn't cause a performance problem (but is obviously the wrong data), adding measure2 when measure1 doesn't filter against the calendar table isn't a performance problem either (but the spread goes on forever and ignores start/end). It seems that the double pass over the calendar table is causing the issues and I'm not a real data guy, just an example junkie, so I'm not sure if there is a more efficient way to achieve the same goal.

I would have to scramble the dataset before sharing so let me know if that's needed.

Any ideas on how to do this more efficiently? I would be a sad dude if I can't make this happen with a relatively small data set.

There might be another way to do it but what specs are you running o the machine?

 

[Ozeroth]

Hi there,

If I've understood what you're doing correctly, an 'Events in progress' type measure would be best in your situation (although you want to sum revenue rather than count events).

This paper has the best performing version I know of on page 27:
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

Adapting the DAX code from that paper to your situation, you should just need a single measure that looks like:

=
SUMX (
    VALUES ( 'Calendar'[Date] ),
    SUMX (
        FILTER (
            GENERATE (
                SUMMARIZE (
                    CombinedOutput,
                    CombinedOutput[StartDate],
                    CombinedOutput[EndDate],
                    "RevenuePerDayTotal", SUM ( CombinedOutput[RevenuePerDay] )
                ),
                DATESBETWEEN (
                    'Calendar'[Date],
                    CombinedOutput[StartDate],
                    CombinedOutput[EndDate]
                )
            ),
            'Calendar'[Date] = EARLIER ( 'Calendar'[Date] )
        ),
        [RevenuePerDayTotal]
    )
)

I tested it out on a dummy model and seemed to perform fine, but you'd better test on your model.

Owen

 

[TheStressMachine]

Thanks Owen, this performs much better, now I'm going to try to understand it

re: specs, I'm on a standard company laptop, but that's the environment our customers will be using, so the performance tune up is critical to the success of this.

Thanks again,
M

 

[TheStressMachine]

Something really strange that happened....

If I select more than one value in any slicer and drill down, I get this error message: Members, tuples, or sets must use the same hierarchies in the function.

When I don't have more than 1 value in a slicer, the drill down works fine.

Any ideas there?

 

[Ozeroth]

I have seen other posts with a similar error but don't know the cause. Could you post a link to a scrambled/sanitised workbook (excel or power bi)?

 

[TheStressMachine]

Here you go.

If you look at the ORG2 Slicer, clearing the filter or selecting just 1 doesn't cause a drilldown problem, however selecting two or more gets the error message.

https://drive.google.com/open?id=0B2YM6pePDxcuaXhZUUk2VnBTRHc

 

[Oscar A]

Here you go.

If you look at the ORG2 Slicer, clearing the filter or selecting just 1 doesn't cause a drilldown problem, however selecting two or more gets the error message.

https://drive.google.com/open?id=0B2YM6pePDxcuaXhZUUk2VnBTRHc

Hi,

Did you find any solution or cause to this multiselect drilldown issue? I'm facing the same problem...

 

[Ozeroth]

TheStressMachine - I don't know why but I totally missed your reply - half a year ago!
I can't reproduce the issue unfortunately No matter how many selections I make on the ORG2 slicer, the PivotTable calculates fine.

I am using Excel 2016 - how about you?

Sorry Oscar A, no explanation as yet from me.

 

[TheStressMachine]

TheStressMachine - I don't know why but I totally missed your reply - half a year ago!
I can't reproduce the issue unfortunately No matter how many selections I make on the ORG2 slicer, the PivotTable calculates fine.

I am using Excel 2016 - how about you?

Sorry Oscar A, no explanation as yet from me.

Issue still persists here. I'm using Excel 2016 64-bit (16.0.6925.1048)