Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
drrai66
Resolver I
Resolver I

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

 

IDEligibility
1Eligible
2N/A
3N/A
4Eligible
5Eligible
6N/A
7Eligible
8Eligible
9N/A
10Eligible

 

Table2

 

ID
1
2
3
4
5
6
7

 

Now I need a Result Column in the Table ! Like This:

 

IDEligibilityResult
1EligibleYes
2N/AYes
3N/AYes
4EligibleYes
5EligibleYes
6N/AYes
7EligibleYes
8EligibleNo
9N/AN/A
10EligibleNo

 

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

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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?Related Table.PNG

Thanks

Deepak

Hi Chris,

Using the Logic in here, I am able to Solve. 

https://community.powerbi.com/t5/Desktop/Geting-first-value-from-one-to-many-related-table-based-on/...

I first Created a Column in Table 1 and Then Used your Logic to Solve it.

Thanks

and Regards

Deepak

Anonymous
Not applicable

Hi @drrai66

 

In the query editor, merge these tables, in the first table or as a new table.

 

mergequeries.png

 

 

merge.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.