Dax measures total mismatch in visual

[sivarajan21]

Hi Team,

I have below dax measures as follows:

Forecast - Target Units_1 = 
VAR _DailyUnits = [Target consumption per day_1]

RETURN 

SUMX(Points,
SUMX(Calendar_,
IF(ISBLANK([Actual Units]),_DailyUnits
   )))

Referenced above measure 'Target consumption per day_1' comes from below:

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
)

The result(Duplicate of Page 3) tab is as below:

Expected output total from Forecast - Target Units_1 should add up(4978+4037) to give 9015 in total.

But the measure total displays as 18,030(guess so) which is not correct & what we wanted. 

Another example for above total mismatch is as below:

I think its to do with filter context or the Data modeling but am struggling to work it out.

Please advise!

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

Thanks in advance!

@marcorusso @Greg_Deckler @amitchandak @Anonymous @Anonymous @jgeddes 

[marcorusso · Accepted solution]

A variable is a constant, it is evaluated only once. 

When are variables evaluated in DAX? - SQLBI

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

[marcorusso · Accepted solution]

A variable is a constant, it is evaluated only once. 

When are variables evaluated in DAX? - SQLBI

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

[sivarajan21]

Hi @marcorusso ,

Thanks for your amazing solution sir!😍
Read this amazing article When are variables evaluated in DAX? - SQLBI and am planning to set up a KT with my team about this article.

This dax returns the correct results as below:

Dax:

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

Result:

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!

@marcorusso @Tutu_in_YYC @Greg_Deckler @amitchandak @Anonymous @Anonymous 

[marcorusso · Accepted solution]

You should consider a snapshot.

See this: Events in progress – DAX Patterns

You should also avoid nested iterators, but the events in progress patter you use produces a non-additive measure that cannot be optimized. If you have million of events, you should go to a snapshot by day, so you can get sub-second performance for your query.

[sivarajan21]

Hi @marcorusso Sir,

I saw your post on linkedin about your legs! I am worried about it!

Hope its not serious! Get well soon!

You will come back stronger! our team prayers are with you!

Wish you speedy recovery💓

I will close this query as of now and accept your solution!

Many thanks

[sivarajan21]

Hi @marcorusso ,

Thanks for your quick response Sir!

I read the entire article on Events-in-progress . It is a fantastic article and contains a lot of detail to learn. 

With help of your article I was able to create below calculated table:

Dax:

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

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 table data for above point id is:

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

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
)

we want to go 'snapshot by day' as we have millions of rows, I am confused how to create 'snapshot by day' you mentioned. Also, I am struggling on how to avoid nested iterators. 

Any guidance would be greatly appreciated!

My expected output is:

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

Please advise!

Thanks in advance!

@marcorusso @Greg_Deckler @amitchandak @jgeddes @Anonymous @Anonymous @Ahmedx 

[Tutu_in_YYC]

Try wrapping Forecast - Target Units_1 with another SUMX([Table/Column that has the name ie Aberdeen-centro court]