Forum Discussion
Return a new Column in Table 1 By Comparing a Column In 2 Tables
Hi Experts,
I am on learning curve for Power BI but a heavy user of Tableau. I need your help in this situtaion:
I have 2 Tables as Follows and both of These Tables are Related on ID
Table1
| ID | Eligibility |
| 1 | Eligible |
| 2 | N/A |
| 3 | N/A |
| 4 | Eligible |
| 5 | Eligible |
| 6 | N/A |
| 7 | Eligible |
| 8 | Eligible |
| 9 | N/A |
| 10 | Eligible |
Table2
| ID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
Now I need a Result Column in the Table ! Like This:
| ID | Eligibility | Result |
| 1 | Eligible | Yes |
| 2 | N/A | Yes |
| 3 | N/A | Yes |
| 4 | Eligible | Yes |
| 5 | Eligible | Yes |
| 6 | N/A | Yes |
| 7 | Eligible | Yes |
| 8 | Eligible | No |
| 9 | N/A | N/A |
| 10 | Eligible | No |
The Logic Is:
If ID(Table1)=ID(Table2) and Eligibility(Table1)="Eligible" THEN "Yes"
Else if
Eligibility(Table1)="Eligible"
THEN "No"
Else if
Eligibility(Table1)="N/A" and ID(Table1)=ID(Table2)
Then "Yes"
Else
"N/A"
END
I need the Similar Formula in POWER BI to get the Result Column in Table 1.
Thanks and Regards
Deepak
Try this:
Column = SWITCH ( TRUE (), AND ( Table1[ID] = RELATED ( Table2[ID] ), Table1[Eligibility] = "Eligible" ), "Yes", Table1[Eligibility] = "Eligible", "No", AND ( Table1[ID] = RELATED ( Table2[ID] ), Table1[Eligibility] = "N/A" ), "Yes", "N/A" )This pattern is the same as a nested IF statement:
SWITCH(TRUE(),
Condition1 being true, Result1,
Condition2 being true, Result2,
...,
Result if everything is false
)
Use AND() to join to logical statements.
Use RELATED() to get the value in the related tables column (roughly similar to a VLOOKUP in Excel)
5 Replies
Hi drrai66,
In the query editor, merge these tables, in the first table or as a new table.
Then create a new custom column using this formula with the logic that you describe:
if (([ID] = [Table2.ID]) and ([Eligibility] = "Eligible"))then "Yes" else if [Eligibility]="Eligible" then "No" else if [Eligibility] = "N/A" and [ID]=[Table2.ID] then "Yes" else "N/A"Remove the Table2.ID column and you will get the table you want.
Regards
Thanks for Your Time Felipe. Thing is my Original Dat aset is not That Simple and Merge is not an Option, but your Calc is going to help me at some other place in my Data.
Regards
Deepak
Try this:
Column = SWITCH ( TRUE (), AND ( Table1[ID] = RELATED ( Table2[ID] ), Table1[Eligibility] = "Eligible" ), "Yes", Table1[Eligibility] = "Eligible", "No", AND ( Table1[ID] = RELATED ( Table2[ID] ), Table1[Eligibility] = "N/A" ), "Yes", "N/A" )This pattern is the same as a nested IF statement:
SWITCH(TRUE(),
Condition1 being true, Result1,
Condition2 being true, Result2,
...,
Result if everything is false
)
Use AND() to join to logical statements.
Use RELATED() to get the value in the related tables column (roughly similar to a VLOOKUP in Excel)
Hi Chris,
This Would Work, I know, but the thing is that those numbers are repeating in my Table 2, so I have One to Many Relationship between Table 1 and Table 2 In My data, So When I write your Calc, I don't get the Name of Table 2 . Any Work Around for this?
Thanks
Deepak
Hi Chris,
Using the Logic in here, I am able to Solve.
I first Created a Column in Table 1 and Then Used your Logic to Solve it.
Thanks
and Regards
Deepak
