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 have two tables that follow this structure:
table1
ID | Dimension | Validation |
1 | AAA | 12334 |
2 | BBB | 23434 |
3 | CCC | xxx |
4 | DDD | 45652 |
5 | EEE | xxx |
table2
ID | Name |
1 | Kim |
2 | John |
3 | Alec |
4 | Phill |
5 | Noah |
The tables are joined by the ID column. I want to use a LOOKUPVALUE on table2 to search the value from the Dimension column on table1, but I want this LOOKUPVALUE to be done only on rows where the column Validation <> "xxx" (is different from "xxx").
My expected result would be like this:
ID | Name | Dimension |
1 | Kim | AAA |
2 | John | BBB |
3 | Alec | |
4 | Phill | DDD |
5 | Noah |
How can I do this?
Solved! Go to Solution.
Hi @Anonymous ,
Try the following formula:
Dimension =
CALCULATE(
MAX(Table1[Dimension]),
FILTER(
Table1,
Table1[Validation] <> "xxx"
&&Table1[ID]=Table2[ID]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @Anonymous ,
if you want that as a measure the code like this would work:
Dimension Value = CALCULATE( MAX( table1[Dimension ] ), table1[Validation] <> "xxx" )
If you want that as a calculated column the following code should work:
Dimension Value Column = IF( RELATED( table1[Validation] ) <> "xxx", RELATED( table1[Dimension] ) )
Hello, @selimovd thanks for the response!
I want to create a calculated column, but when I try to use this code that you created, the RELATED function does not give me any option to be searched in the table1, as if I had not even linked. But when I make this same calculated column in table1, the RELATED function brings me all the values in table2.
The link for my table1 and table2 are like:
table1 * ----------1 table2
but the Dimension column of table1 always shows only two possible values for the same ID: the actual value that I want to get and the value "xxx".
For example: for the column in table1 with ID=1
ID | Dimension | Validation |
1 | AAA | 12334 |
1 | AAA | 12334 |
1 | BBB | xxx |
1 | AAA | 12334 |
1 | CCC | xxx |
so, the RELATED function in table2 should bring me the Dimension = AAA
Hi @Anonymous ,
Try the following formula:
Dimension =
CALCULATE(
MAX(Table1[Dimension]),
FILTER(
Table1,
Table1[Validation] <> "xxx"
&&Table1[ID]=Table2[ID]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |