cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rylach
Helper II
Helper II

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

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.

 

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.
1 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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
vanessafvg
Super User I
Super User I

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

 

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

@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.

"First they ignore you. Then they laugh at you. Then they fight you. Then you win." - Mohandas Gandhi.

@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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors