Sum Where version is highest by another variable that can be split by another columns

Question

As a continuation to this question:

I would like to have a measure that will sum the Value only for the max version of each house.

So following this example table (data table):

|---------------------|------------------|------------------|------------------|
|      House_Id       |     Version_Id   |     Color_Id     |       Value      |
|---------------------|------------------|------------------|------------------|
|          1          |         1        |    1 (Green)     |       1000       |
|---------------------|------------------|------------------|------------------|
|          1          |         2        |    2 (Red)       |       2000       |
|---------------------|------------------|------------------|------------------|
|          2          |         1        |    1 (Green)     |       3000       |
|---------------------|------------------|------------------|------------------|
|          3          |         1        |    1 (Green)     |       5000       |
|---------------------|------------------|------------------|------------------|

The result of this measure should be: 10.000 because the house_id 1 version 1 is ignored as there's another version higher.

If there were more versions, the measure should only take into account the highest of each house.

By House_id the result should be (Again, House_Id 1 / Version 1 is ignored):

|---------------------|------------------|
|      House_Id       |     Value        |
|---------------------|------------------|
|          1          |       2000       |
|---------------------|------------------|
|          1          |       3000       |
|---------------------|------------------|
|          2          |       5000       |
|---------------------|------------------|
|        Total        |      10000       |
|---------------------|------------------|

I also want this measure to be capable of showing the result when using another variable of the Data table (or related tables), but maintaining the logic of Max version per House.

As shown on the example table before I have the Color_Id column. This Color_Id in the main table is connected to a Color table that contains the color name.

If I add a visual table with ColorName (from the ColorTable) and the measure, the result should be as follows:

|---------------------|------------------|
|      ColorName      |      Value       |
|---------------------|------------------|
|        Green        |       8000       |
|---------------------|------------------|
|        Red          |       2000       |
|---------------------|------------------|
|        Total        |       10000      |
|---------------------|------------------|

With the solution provided in the other question, the result is correct on the Total row, but is wrong because it does not show the correct value for each color. The following table is the result of applying the measure in the question provided (wrong result):

|---------------------|------------------|
|      ColorName      |      Value       |
|---------------------|------------------|
|        Green        |       9000       |  <- Error Here
|---------------------|------------------|
|        Red          |       2000       |
|---------------------|------------------|
|        Total        |       10000      |
|---------------------|------------------|

This result is wrong per ColorName as 9000 + 2000 is 11000 and not 10000. The measure should ignore the rows with an old version per house. In the example before this is the row for House_Id 1 and Color_Id Green because the version is old (there's a newer version for that House_Id).

So:

How can I change the measure so it shows the correct value per Color_Id as well as per House_Id and as a total?
In the data table I have more columns. What If I want to filter by another column from (or related to) the Data table such as Location_Id? It is posible to define the measure in such a way that could work for any given number splits for columns in the main Data table?

Help is greatly appreciated here

EDIT: The solution provided by Alexis Olson works when the Data table is imported. When the Data table is connected with DirectQuery mode, it won't work.

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-13T13:53:20

In RADO's answer, the issue is with the first variable.

Sum of Latest Values =
VAR Latest_Versions =
    SUMMARIZE ( Data, Data[House_id], "Latest_Version", MAX ( Data[Version_Id] ) )

VAR Latest_Values =
    TREATAS ( Latest_Versions, Data[House_id], Data[Version_Id] )

VAR Result =
    CALCULATE ( SUM ( Data[Value] ), Latest_Values )

RETURN Result

The Data table as the first argument in the SUMMARIZE is not the whole table but evaluated within the local filter context. This means that when you are in the Green row in your table, it doesn't see Version_ID = 2 and thus includes the first version in the Green row but not in the total (which sees all of the rows).

The fix is quite simple -- remove the local filter context from that first table argument. One way to do this is to use ALL ( Data ) instead of just Data. This is likely not the most memory-efficient though and you may prefer to write something like this instead:

Sum of Latest Values =
VAR Latest_Versions =
    ADDCOLUMNS (
        VALUES ( Data[House_Id] ),
        "Latest_Version",
        CALCULATE ( MAX ( Data[Version_Id] ), ALLEXCEPT ( Data, Data[House_Id] ) )
    )
VAR Latest_Values =
    TREATAS ( Latest_Versions, Data[House_id], Data[Version_Id] )
VAR Result =
    CALCULATE ( SUM ( Data[Value] ), Latest_Values )
RETURN
    Result

Sum Where version is highest by another variable that can be split by another columns

1 Answers