Guidance opn lookup table using DAX query.

[Bansi008]

Hi,

I want to populate Modifier column values in the table 2 through lookup funcationality based identifier. Identifier value is not unique string so where identifier comes code should populate value from modifier column in table 2.

even if same identifier on multiple records it still works and fetch the modifier value in table 2. 

How could i do that in PowerBI using DAX query?

Please note I have built my workflow on dashboard view, not in the transform view. 

Thanks!

Table 1

To_Date	IDENTIFIER	ACCOUNT_ID	MODEL_ID	Modifier
2/29/2024	100+201	100	201	0.12
2/29/2024	105+210	105	210	0.23
2/29/2024	110+220	110	220	0.25
2/29/2024	100+201	100	201	0.12
2/29/2024	115+230	115	230	0.3
2/29/2024	105+210	105	210	0.23
2/29/2024	110+220	110	220	0.25

Table 2

Table 2					Expected Output
To_Date	IDENTIFIER	ACCOUNT_ID	MODEL_ID		Modifier_Lookup
3/31/2024	100+201	100	201		0.12
3/31/2024	105+210	105	210		0.23
3/31/2024	110+220	110	220		0.25
3/31/2024	100+201	100	201		0.12
3/31/2024	115+230	115	230		0.3
3/31/2024	105+210	105	210		0.23
3/31/2024	110+220	110	220		0.25

[TomMartens · Accepted solution]

Hey @Bansi008 ,

I use the below DAX statement to create a calculated column in Table2:

Modifier_Lookup_Tom = 
var currentIdentifier = 'Table2'[IDENTIFIER]
return
AVERAGEX(
    SUMMARIZE(
        FILTER( 'Table1' , 'Table1'[IDENTIFIER] = currentIdentifier )
        , Table1[IDENTIFIER]
        , Table1[Modifier]
    )
    , Table1[Modifier]
)

The screenshot shows Table 2:

Because you are looking for a scalar value, meaning a single value, instead of rows, you have to define how this single value must be calculated; in my above example, I decided to take the average by leveraging the DAX function AVERAGEX. Of course, you can also use MINX or MAXX. The aggregation is necessary as multiple values might be returned by the filter statement FILTER( 'Table1' , 'Table1'[IDENTIFIER] = currentIdentifier).

The complexity of the aggregation is defined by your business rules and only limited by your imagination.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[Bansi008]

Hi Tom,

Let me try this solution and come back to you.

Thanks for your help