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.
I am working with a very large enterprise database which has multiple lookup tables. These tables are typically two columns, one for the value's ID and its corresponding name. what is the best approach for returning values from a table like this? Using LOOKUPVALUE is not suitable becuase the lookup value is not scalar and creating many relationships between the lookup table and associated tables is a nuisance.
Solved! Go to Solution.
Hi @Anonymous ,
If there are existed relationships among these tables, you may create column or measure in Table1 like DAX below using FIRSTNONBLANK function.
Column1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ),FILTER ( ALL ( Table2 ), Table2[linked field] = Table1[linked field] ))
Measure1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ), FILTER ( ALL ( Table2 ), Table2[linked field] = MAX(Table1[linked field] )))
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If there are existed relationships among these tables, you may create column or measure in Table1 like DAX below using FIRSTNONBLANK function.
Column1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ),FILTER ( ALL ( Table2 ), Table2[linked field] = Table1[linked field] ))
Measure1= CALCULATE (FIRSTNONBLANK ( Table2[goal field], 1 ), FILTER ( ALL ( Table2 ), Table2[linked field] = MAX(Table1[linked field] )))
Best Regards,
Amy
Community Support Team _ Amy
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 |
---|---|
111 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |