Return a summarized value from a related table via a many to one relationship in Power BI (DAX)

1
votes

I'm working with PowerBI, but considering this is a Dax question I'd assume this applies to power pivot as well.

Consider the following tables, with the desired result in Table - One:

Table - Many

+----+-------+
| id | value |
+----+-------+
| 1  | a     |
+----+-------+
| 1  | a     |
+----+-------+
| 1  | a     |
+----+-------+

Table - One

+----+-----------------------+
| id | minValueFromTableMany |
+----+-----------------------+
| 1  | (Expecting a)         |
+----+-----------------------+

I have a relationship setup between the two tables. I'd like to derive a new attribute in 'Table - One' that is simply the min on 'value' in table - many for each matching id.

This would be trivial in any SQL-varient, Python or R.

SQL

SELECT
    T1.id
    ,T2.minValueFromTableMany
FROM TableOne T1
    INNER JOIN ( 
                 SELECT MIN(value) as minValueFromTableMany
                 FROM TableMany
                 GROUP BY id
               ( as T2
       ON T1.id = T2.id

How do we do this in DAX? Using a min() function simply returns the min(value) for the entire column and is not using the relationship I've setup.

1
powerbidaxpowerpivot

1 Answers

2
votes

It's even simpler in DAX. You can create a calculated column like this:

minValueFromTableMany = CALCULATE(MIN(TableMany[value]),
                            ALLEXCEPT(TableOne, TableOne[id]))

This assumes that they have a relationship on the id columns.

Here's another way to do it that should work with or without the relationship:

minValueFromTableMany = CALCULATE(MIN(TableMany[value]),
                            TableMany[id] = EARLIER(TableOne[id]))

The EARLIER function refers to the earlier row context.

You can also use the MINX function:

minValueFromTableMany = MINX(
                            FILTER(TableMany, TableMany[id] = TableOne[id]),
                            TableMany[value])