Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
@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
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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |