Dax measures total mismatch in visual level

Hi SergeiBaklan

Thanks for your amazing solution sir!😊

This returns the correct results as below:

When I keep only the top(high) level filter (i.e) DBName, this new measure takes long time to run & causes performance issues as below:

When i ran this query in dax studio, it was taking long time to run:

I think if function inside SUMX function is causing the performance issues especially when our data has millions of rows.

But I am struggling to resolve it. 

Please advise!

PFA file here with new measure Portfolio Performance - v2.13 - Copy.pbix

Thanks in advance!

SergeiBaklan 

Hi SergeiBaklan ,

I have found a solution to improve the performance sir!

Based on the article Events-in-progress I was able to create a day based calculated table as below:

Target Timeseries_1 = 
SELECTCOLUMNS (
    GENERATE (
        TargetTimeSeries,
        DATESBETWEEN (
            Calendar_[Date],
            TargetTimeSeries[StartDate],
            TargetTimeSeries[EndDate] 
        )
    ),
    "DBName-PointID",TargetTimeSeries[Point_Id],
    "Usage", TargetTimeSeries[Usage],
    "Dates",Calendar_[Date],
    "DBName",TargetTimeSeries[DBName]
)

If we avoid nested iterators we can achieve the performance. As there are million of rows in my case, we should go to a snapshot by day from above article. 

Result:

Also, I have created a relationship between this table & the calendar & the points table as below:

Now what i am struggling here is how to move on from here to recreate/modify the two existing dax measures to adjust/incorporate to the newly created calculated table 'Target Timeseries_1'. 

what is confusing me is for example, in below dax,

Number of days between start & end date and divide the usage column with that. But in our new target table we have the date column that contains those days in single column.

For example, Point Id NetworkRail-43230 Starts from 02/05/2023 & ends on 27/05/2023 similar to the original targettimeseries table as below:

Original targettimeseries data for same NetworkRail-43230 as below:

Also, I guess we don't need below keep filters as calendar is connected directly to our new calculated table.

We have tried above solutions by reading through this events in progress article. Not sure whether we have done correctly as per the article. But this article is the key to solving our problem.

Please guide us sir!

Existing dax that needs to be modified to achieve expected output as per new calculated table:

Forecast - Target Units_2 = 
    SUMX (
        Points,
        SUMX (
            Calendar_,
            IF (
                ISBLANK ( [Actual Units] ),
                [Target consumption per day_1]
            )
        )
    )

Target consumption per day_1 = 
VAR minDate = MIN ( 'Calendar_'[Date] )
VAR maxDate = MAX ( 'Calendar_'[Date] )
RETURN  CALCULATE (
    SUMX ( 
        TargetTimeSeries,
        VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate] +1
        VAR Result = TargetTimeSeries[Usage] / _Days
        RETURN Result
    ),
    REMOVEFILTERS ( 'Calendar_' ),
    KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ),
    KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate ),
    TargetTimeSeries[TargetType] = 1
)

My expected output is:

PFA file here Portfolio Performance - v2.13 - Copy.pbix

Please advise!

Thanks in advance!

SergeiBaklan