Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi There,
I have two source of table which i would like to combine my data.
Table 1 contains information of the actual spend of the product code and the sourcing status. The principal name has an error for all code that has 'Single' as their sourcing status:
Code | Spend | Principal | Sourcing Status |
1010 | 1000 | Agent | Single |
1013 | 2000 | Agent | Single |
1043 | 1400 | Agent | Single |
1238 | 300 | Agent | Single |
1269 | 2344 | Principal A | Multiple |
1269 | 3045 | Principal D | Multiple |
1138 | 450 | Principal A | Multiple |
1138 | 130 | Principal B | Multiple |
1123 | 450 | Principal C | Multiple |
1123 | 444 | Principal D | Multiple |
Table 2 has the correct principal information:
Code | Principal | Sourcing Status |
1010 | Principal A | Single |
1013 | Principal B | Single |
1043 | Principal C | Single |
1238 | Principal A | Single |
1269 | Principal A | Multiple |
1269 | Principal D | Multiple |
1138 | Principal A | Multiple |
1138 | Principal B | Multiple |
1123 | Principal C | Multiple |
1123 | Principal D | Multiple |
How do i use Lookupvalue function to get the principal value for code that has "Single" status? Lookupvalue will return error if it lookup for code that has "Multiple" status as it will return multiple value where a single value is expected.
Desired output:
Code | Spend | Principal | Correct Principal |
1010 | 1000 | Agent | Principal A |
1013 | 2000 | Agent | Principal B |
1043 | 1400 | Agent | Principal C |
1238 | 300 | Agent | Principal A |
1269 | 2344 | Principal A | Principal A |
1269 | 3045 | Principal D | Principal D |
1138 | 450 | Principal A | Principal A |
1138 | 130 | Principal B | Principal B |
1123 | 450 | Principal C | Principal C |
1123 | 444 | Principal D | Principal D |
Solved! Go to Solution.
Hi @Anonymous ,
To create a calculated column in Table 1.
Column = IF ( 'Table1'[Sourcing Status] = "Single", LOOKUPVALUE ( Table2[Principal], Table2[Code], Table1[Code] ), Table1[Principal] )
Hi @Anonymous ,
To create a calculated column in Table 1.
Column = IF ( 'Table1'[Sourcing Status] = "Single", LOOKUPVALUE ( Table2[Principal], Table2[Code], Table1[Code] ), Table1[Principal] )
hI @Anonymous
Try the following measure
Measure = VAR __complete = "Single" RETURN CALCULATE( MAX( Table2[Principal] ), CUSTOMERS[Sourcing Status] = __complete )a little trick I learned this week
Hi @Anonymous ,
The solution you proposed only returns one principal from Table 2.
Now the code with the "Multiple" status is also returning Principal C, whereas i originally wanted the previous correct principal to remain.
Any ideas on how to solve this?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |