Forum Discussion
Lookup text from another table based on highest value
Hi All, I am trying to create a column that shows the highest priority contact from another table. If there's a tie for highest priority, I would like to get the first one. Here's what my data loo...
Hi Anonymous ,
We can insert an index column in power query in table 1. Then we can create a calculated column as below.
Column = VAR pr = CALCULATE ( MAX ( 'Table 1'[Priority] ), FILTER ( 'Table 1', 'Table 1'[company ID] = 'Table 2'[ID] ) ) VAR minindex = CALCULATE ( MIN ( 'Table 1'[Index] ), FILTER ( 'Table 1', 'Table 1'[Priority] = pr && 'Table 1'[company ID] = 'Table 2'[ID] ) ) RETURN CALCULATE ( MAX ( 'Table 1'[Contact Name] ), FILTER ( 'Table 1', 'Table 1'[Priority] = pr && 'Table 1'[Index] = minindex ) )Pbix as attached.
Anonymous add following measure
Contact = CALCULATE( MIN( Contact[Contact Name] ), TOPN(1, Contact, Contact[Priority], DESC ) )