PowerBI DAX – subquery to the same table

1
votes

I have a table, let's call it Products with columns:

  • Id
  • ProductId
  • Version
  • some other columns…

Id column is the primary key, and ProductId groups rows. Now I want to view distinct values of ProductId where Version is highest.

I.e. From data set:
Id | ProductId | Version | ...
100 | 1 | 0 | ...
101 | 2 | 0 | ...
102 | 2 | 1 | ...
103 | 2 | 2 | ...

I need to get:
Id | ProductId | Version | ...
100 | 1 | 0 | ...
103 | 2 | 2 | ...

In SQL I would write:

SELECT Id, ProductId, Version, OtherColumns
FROM Products p1 
WHERE NOT EXISTS 
(SELECT 1 
 FROM Products p2
 WHERE p2.ProductId = p1.ProductId
 AND p2.Version > p1.Version)

But I have no idea how to express this in DAX. Is this approach with subqueries inapplicable in PowerBI?

2
powerbidax

2 Answers

0
votes

Another approach is to first construct a virtual table of product_ids and their latest versions, and then use this table to filter the original table:

EVALUATE 
VAR Latest_Product_Versions = 
      ADDCOLUMNS(
         VALUES('Product'[Product_Id]),
         "Latest Version", CALCULATE(MAX('Product'[Version])))

RETURN 
CALCULATETABLE(
    'Product', 
    TREATAS(Latest_Product_Versions, 'Product'[Product_Id], 'Product'[Version]))

Result:

enter image description here

The benefit of this approach is optimal query execution plan.

0
votes

You can use SUMMARIZECOLUMNS to group ProductId and MAX Version.

Then use ADDCOLUMNS to add the corresponding Id number(s), using a filter on the Products table for the matching ProductId and Version. I've used CONCATENATEX here, so that if multiple Id values have the same Product / MAX Version combination, all Id values will be returned, as a list.

EVALUATE 
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 
            Products[ProductId],
            "@Max Version",
            MAX ( Products[Version] )
        ),
        "@Max Version Id",
        CONCATENATEX ( 
            FILTER ( 
                Products,
                Products[Version] = [@Max Version] && Products[ProductId] = EARLIER ( Products[ProductId] )
            ),
            Products[Id],
            ","
        )
    )