Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
Hi Tom,
Let me try this solution and come back to you.
Thanks for your help
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |