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.
Hello
I've got a table that looks like this:
Column A | Column B | Column C | Calculated Column |
3 | 99 | P 18 | =LOOKUPVALUE([Column C];[Column A];1;[Column B];"99") |
4 | 88 | P 144 | =LOOKUPVALUE([Column C];[Column A];1;[Column B];[Column B]) |
2 | 77 | P 2 | |
2 | 77 | P 2 | |
1 | 88 | P 1 | |
1 | 99 | P 4 | |
2 | 44 | P 5 | |
3 | 22 | P 7 | |
1 | 88 | P 99 |
My goal is that the LOOKUPVALUE finds the row where in [Column A]=1 and [Column B] is the same value as on its own row, and then gives [Column C] as output.
So the first row would give "P 4" as output. This works as long there arent 2 possible entries in the table. In this case, i just want the first one. The Second row would give "P 1" then.
Can someone help?
Solved! Go to Solution.
You should use EARLIER() in your filter:
Column = CALCULATE ( FIRSTNONBLANK ( Table4[Column C], TRUE () ), FILTER ( Table4, Table4[Column A] = 1 && Table4[Column B] = EARLIER ( Table4[Column B] ) ) )
Regards,
You can try the FIRSTNONBLANK function instead of LOOKUPVALUE. This would look like:
CALCULATE( FIRSTNONBLANK(ColumnC, TRUE()), FILTER(Table, ColumnA = ColumnB) )
Hello
I tried what u said and used
=CALCULATE( FIRSTNONBLANK('Table'[Column C]; TRUE()); FILTER('Table';'Table'[Column A]=1); FILTER('Table';'Table'[Column B]='Table'[Column B]) )
It works half. It ignores kinda the second Filter, so the output currently is "P 1" for every line. If i replace the second 'Table'[Column B] with the actual Value of the Row (e.g. 99 for the first row, 88 for the second one...) it shows the correct value.
Any other Ideas?
You should use EARLIER() in your filter:
Column = CALCULATE ( FIRSTNONBLANK ( Table4[Column C], TRUE () ), FILTER ( Table4, Table4[Column A] = 1 && Table4[Column B] = EARLIER ( Table4[Column B] ) ) )
Regards,
Hi ,
In the same dax I want to add a condition to compare the column value, how to do that,
this is my formula
I am working on a patient flow projected and was having such a hard time looking up the earliest date of a consult based on patient number and visit code (consult vs. treatment vs. simulation). This solution worked for me! Thank you so much.
Hi, All
I have used this approach and it is partly working for me. I would like to extract the the value of the column Z_result by filtering in Variable 1 and Variable 2 (first value of Z_Results when the values of Variable 1 and Variable 2 are equal).
By using FIRSTNONBLANK function, I am not getting what I want (see Unique ID 7 and 8 in the attached picture).
The attached pic is just an oversimplication of a much more complex set of data with 1000s of rows
Thanks for your help
------------------------------
This works perfect, thank you very much
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |