Calculation group showing change linked by version number

[Blaenzo]

Hi,

My tables have SCD type 2 with a version ID that links versions of the same record:

My goal is to create a calculation group that returns for each measure in a table the difference between the (current) version and the previous version (like columns 'PriceChange' and 'QuantityChange' in the example above).

I know this is some form of row level calculation but I am stuck at how to calculate this. If necessary I would be able to add a column with a reference to the (unique) ID of the corresponding previous version row.

I would like to stay away from calculated columns because some tables have hundreds of measures. A calculation group would allow me to apply the same logic to all measures and all tables within the data model.

Thanks for all the help.

Martijn

[Anonymous]

Hi, @Blaenzo 

Based on your description, I have created many measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

PriceMeasure =

CALCULATE ( SUMX ( 'Table', 'Table'[Price] ) )



QuantityMeasure =

CALCULATE ( SUMX ( 'Table', 'Table'[Quantity] ) )



VersionDiff =

VAR _prod_id =

    SELECTEDVALUE ( 'Table'[ProductID] )

VAR _id =

    SELECTEDVALUE ( 'Table'[ID] )

VAR _ver =

    SELECTEDVALUE ( 'Table'[Version] )

VAR _pre_ver =

    CALCULATE (

        MAX ( 'Table'[Version] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[Version] = _ver - 1

                && 'Table'[ProductID] = _prod_id

        )

    )

VAR _diff_ver =

    IF ( NOT ISBLANK ( _pre_ver ), _ver - _pre_ver )

RETURN

    _diff_ver





PriceDiff =

VAR _ID =

    MAXX ( FILTER ( ALL ( 'Table' ), [ID] < SELECTEDVALUE ( 'Table'[ID] ) ), [ID] )

VAR _previousDiff =

    CALCULATE (

        SUM ( 'Table'[Price] ),

        FILTER ( ALL ( 'Table' ), 'Table'[ID] = _ID )

    )

VAR _diff =

    CALCULATE ( 'Table'[PriceMeasure] - _previousDiff )

RETURN

    IF ( [VersionDiff] = 1, _diff )





QuantityDiff =

VAR _ID =

    MAXX ( FILTER ( ALL ( 'Table' ), [ID] < SELECTEDVALUE ( 'Table'[ID] ) ), [ID] )

VAR _previousDiff =

    CALCULATE (

        SUM ( 'Table'[Quantity] ),

        FILTER ( ALL ( 'Table' ), 'Table'[ID] = _ID )

    )

VAR _diff =

    CALCULATE ( 'Table'[QuantityMeasure] - _previousDiff )

RETURN

    IF ( [VersionDiff] = 1, _diff )

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Blaenzo]

Hi Yang, thanks for your reply.

I have succesfully tested your approach. However, 'SELECTEDVALUE()' only works if the row context contains only one row for the relevant fields. This approach no longer works at an aggregate level, e.g. in a matrix.

So I decided to use the OFFSET function as described here:

Understanding ORDERBY, PARTITIONBY, and MATCHBY functions in DAX - DAX | Microsoft Learn

For example, for the following table:

the measure

PrevVal = CALCULATE(SUM(TestTable1[Val]),OFFSET(-1,,ORDERBY(TestTable1[VERSION], ASC),,PARTITIONBY(TestTable1[LeaseID])))

gives

and aggregated by version:

However, I am using SSAS Tabular 1600 (version 16.0.43.222) and the implemenation of OFFSET in SSAS appears to be riddled with bugs and incomplete vs PowerBI (e.g. MATCHBY() is not supported).

Bugs that I ran into:

1. both PARTITIONBY() and ORDERBY() won't work with integers (like in my case LeaseID and VERSION).
   Changing those fields to STRING in the datamodel (not in the actual sql table) resolved this issue.
2. A table and matrix visual will crash if (sub-)totals are enabled, while this works fine using PowerBI directly linked to the same DB table (i.e. without an SSAS data model).
3. Trying to work around this using IFERROR() and ISERROR() does not work and the measure will return an error (i.e. the visual crashes) regardsless. IFERROR() not catching errors happened in the past for certain functions until that was fixed if I remember correctly.
4. Probably related to 2) above; aggregation in a matrix does not work
5. Evaluating a DAX statement in SSMS with an explicit measure containing OFFSET fails, while the exact same measure created in SSAS runs fine in that exact same DAX query. This should be equivalent.
6. Using OFFSET in a calculation group works to some extent but has a significant flaw, possibly related to 3) above:
   Assume two calculaton items 'Current' and 'Prev':

Current=SELECTEDMEASURE()

Prev=
    IF(
        ISSELECTEDMEASURE([Sum of Val]),
        CALCULATE(SELECTEDMEASURE()),OFFSET(-1,,ORDERBY(TestTable1[VERSION], ASC),,PARTITIONBY(TestTable1[LeaseID]))),
        SELECTEDMEASURE()
    )

Then, if only 'Current' is selected, 'Prev' is evaluated regardless and results in the visual crashing if the OFFSET calculation is not applicable in a given filter context. This is very strange.

Also, when another measure is included (e.g. [Sum of Val2]), for calculation item 'Prev' (or 'Current'), the IF statement should simply return 'SELECTEDMEASURE()' and skip the evaluation of the OFFSET formula. However, the IF statement does evaluate it leading to an error, which conflicts with the implementation of the IF statement. This makes the current usablilty if the OFFSET function in a calculation group very limited.

All these situations typically result in the following error even when there is one match:

PartitionBy column 'LeaseID' does not match any row context or filter context. OrderBy and PartitionBy columns must have exactly one match.

What would be the best way to raise these bugs with Microsoft?

Thank you.