2

I'm struggling having this measure to work.

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:

|---------------------|------------------|------------------|
|      House_Id       |     Version_Id   |     Value        |
|---------------------|------------------|------------------|
|          1          |         1        |       1000       |
|---------------------|------------------|------------------|
|          1          |         2        |       2000       |
|---------------------|------------------|------------------|
|          2          |         1        |       3000       |
|---------------------|------------------|------------------|
|          3          |         1        |       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.

By House_id the result should be:

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

Can anyone help me?

EDIT:

Given the correct answer @RADO gave, now I want to further enhance this measure:

Now, my main Data table in reality has more columns. What if I want to add this measure to a table visual that splits the measure by another column from (or related to) the Data table.

For example (simplified 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       |
|---------------------|------------------|------------------|------------------|

There's a Color_Id in the main table that is connected to a Color table. Then I add a visual table with ColorName (from the ColorTable) and the measure (ColorId 1 is Green, 2 is Red).

With the given answer the result is wrong when filtered by ColorName. Although the Total row is indeed correct:

|---------------------|------------------|
|      ColorName      |      Value       |
|---------------------|------------------|
|        Green        |       9000       |
|---------------------|------------------|
|        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. 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:

  1. How can I address this situation?
  2. 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?
Improve this question
4
  • Related: stackoverflow.com/questions/52525377/… Commented Apr 23, 2020 at 18:11
  • @AlexisOlson This link refers to the TOP row, but I want the row where version is the maximum version on a group. Commented Apr 28, 2020 at 8:44
  • You just need to filter the table you're applying TOPN to then. Commented Apr 28, 2020 at 13:21
  • But you do need to sum as well as another step, so this isn't a duplicate. Commented Apr 28, 2020 at 13:26

1 Answer 1

Reset to default
2

I use "Data" as a name of your table. 

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

Measure output:

enter image description here

How it works:

  1. We calculate a virtual table of house_ids and their max versions, and store it in a variable "Latest_Versions"
  2. We use the table from the first step to filter data for the latest versions only, and establish proper data lineage (https://www.sqlbi.com/articles/understanding-data-lineage-in-dax/)
  3. We calculate the sum of latest values by filtering data for the latest values only.

You can learn more about this pattern here: https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

now I want to add another column, like Color_Id and separate the latest values by this column in a table. It appears that the total row is ok (only shows the latest value), but each color_id shows the sum of all the versions (not just the latest).
Where is Color_id column coming from? The same table?
Yes, same table, but I have the color name in a related table joined by color id
@janscas - conceptually, you need to add color id to the first two variables, the same way house_id was used. If you need more help to figure this out, it'll be easier to help you if you post it as a new question with a new data sample and desired result.
ok @RADO, but if I add the Color_Id to the first two variables then the max version will work per House & color and that's not what I want. The max is per House only, but allow me to crossfilter by Color.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.