Geting first value from one-to-many related table based on another column

[rylach]

Hi,

I have a table, let's say

table_A:

id int

and related

table_B:

id int

table_A_id int

label text

created date

with one-to-many relation on  table_A(id) -> table_B(table_A_id).

I'd like to add a new column to table_A containig for every row the first one (basing on 'created' column) value of 'label'. Quite simple with SQL, but how to do it in DAX?

TIA.

Ryszard.

[vanessafvg]

@rylach  what does your data look like?

so you saying you want the min label value for every id, or you want the first one in the sequence?

might be useful if you show me how you do this in sql so i can understand what you saying

[rylach]

Example data in Table_B:

id table_A_id label created

1 2 A 2017-03-01

2 3 C 2017-04-07

3 3 E 2017-05-13

4 3 X 2017-01-03

5 4 A 2017-03-01

For particular table_A_id the new column should have values:

for table_A_id=2: A

for table_A_id=3: X (youngest created for this one id)

for table_A_id=4: A

[rylach]

@vanessafvg, I tried to create a calculated column with the formula:

first_label = CALCULATE(

MIN(Table_B[label]);

FILTER(Table_B;Table_B[created]=MIN(Table_B[created]))

)

(MIN in row 2. is only for syntax matching, I thought that FILTER returns only one row, so that one MIN has no meaning)

I supposed, that using CALCULATE to create a context transition makes the inner FILTER function work only on rows from Table_B related to the currently processed row from Table_A. But, apparently, this does not work that way. If I add a row to Table_B 

5 Z 2016-01-01

then value of first_label is for every row 'Z', meaning that the FILTER operates on whole Table_B, not just related rows.

Do you know why?

And, how to make it operate only on the related ones ?

TIA

R.

[OwenAuger · Accepted solution]

@rylach

To answer your question, FILTER( Table_B, ... ) was evaluated in the original filter context before context transition, so the entire Table_B is filtered, rather than the related rows of Table_B related to the current row of Table_A

This article covers this point:

http://www.sqlbi.com/articles/context-transition-and-expanded-tables/

"CALCULATE executes context transition, but its filter parameters get evaluated in the original filter context, not in the one modified by CALCULATE."

To make your existing code work, you can wrap the entire expression in CALCULATE.

first_label =
CALCULATE (
    CALCULATE (
        MIN ( Table_B[label] );
        FILTER ( Table_B; Table_B[created] = MIN ( Table_B[created] ) )
    )
)

The outer CALCULATE results in context transition, ensuring that the inner CALCULATE is evaluated in a filter context equivalent to the current row of Table_A which includes the related rows of Table_B.

I also personally like to use FIRSTNONBLANK/LASTNONBLANK to return a table containing min/max values in a column (and to arbitrarily break ties), as the code is a bit shorter:

first_label =
CALCULATE (
    CALCULATE (
        FIRSTNONBLANK ( Table_B[label]; 0 );
        FIRSTNONBLANK ( Table_B[created]; 0 )
    )
)

Regards,

Owen