Forum Discussion

Bansi008's avatar
Bansi008
Icon for Helper III rankHelper III
2 years ago
Solved

Guidance opn lookup table using DAX query.

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_DateIDENTIFIERACCOUNT_IDMODEL_IDModifier
2/29/2024100+2011002010.12
2/29/2024105+2101052100.23
2/29/2024110+2201102200.25
2/29/2024100+2011002010.12
2/29/2024115+2301152300.3
2/29/2024105+2101052100.23
2/29/2024110+2201102200.25

 

Table 2

 

Table 2    Expected Output
      
To_DateIDENTIFIERACCOUNT_IDMODEL_ID Modifier_Lookup
3/31/2024100+201100201 0.12
3/31/2024105+210105210 0.23
3/31/2024110+220110220 0.25
3/31/2024100+201100201 0.12
3/31/2024115+230115230 0.3
3/31/2024105+210105210 0.23
3/31/2024110+220110220 0.25

 

 

Need Help
  • TomMartens's avatar
    TomMartens
    2 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Bansi008
      Icon for Helper III rankHelper III
      2 years ago

      Hi Tom,

      Let me try this solution and come back to you.

      Thanks for your help