Forum Discussion
Helper IVMeasure and lookup
I have 2 tables with a many to one relationship. I need to lookup and ID from table 1, find it table 2, and return the MEASURE associated with the ID.
I have read that you cannot use a measure when using the LOOKUP function and I don't understand how RELATED will allow to return a value based on the related column.
Does anyone know a work around or a better solution to the dilemma?
Any help will be greatly appreciated.
6 Replies
ryan_mayuSuper User
could you pls provide some sample data and expected output?
ryan_mayu , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi unknown917 ,I created some data:
Here are the steps you can follow：
1. Create measure.
Measure = SUMX( FILTER(ALL('Table2'),'Table2'[ID]=MAX('Table1'[ID])),[Value])2. Result:
If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
unknown917The above measure resulted in 'Infinity'.
Below is an example of my dataset.
Table 1 ID 1 2 3 1 4 3 2 6 7 5 5 8 Table 2 ID Value (measure) 1 15 2 23 3 78 4 54 5 2 6 36 7 14 8 5 - unknown917
To clarify again, I used the what if parameter for the purposes of data entry at the end user level. The expected output would be:
Table 1 ID Output 1 15 2 23 3 78 1 15 4 54 3 78 2 23 6 36 7 14 5 2 5 2 8 5
- unknown917
I should clarify as well. The measure is from a what if parameter.
Thank youy
Hi unknown917 ,
Use the following measure in the sample data I created to show the correct values:
Measure = SUMX( FILTER(ALL('Table2'),'Table2'[ID]=MAX('Table1'[ID])),[Value_Measure])Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Note: Power BI Table Visual will automatically aggregate duplicates, so to have all [IDs] in Table1 displayed, you can create an index in Power Query to set a separate label for each row.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
