cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Tagging rows in power bi with a calculated column

Table1

 Code Name 123 Apple 124 Orange 125 Banana 126 Grapes 127 Pineapple 128 Melon

Table2

 Code Name 123 Apple 124 Orange 125 Tomato 126 Spinach 127 Pineapple 128 Garlic

I want to compare the two tables and basically make a calculated column in which it would do the comparison based on the Code column, so it will look at the code and check its values in the Name column. For example if the name values of a Code are different then it will be tagged as 0 whereas if the values are the same then it will be tagged as 1. It would look something like this.

Table1

 Code Name Tag 123 Apple 1 124 Orange 1 125 Tomato 0 126 Grapes 0 127 Pineapple 1 128 Melon 0

Table2

 Code Name Tag 123 Apple 1 124 Orange 1 125 Banana 0 126 Spinach 0 127 Pineapple 1 128 Garlic 0

How do I achieve this?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

You can create the calculated columns as below in Table1 and Table2 separately, please find the details in the attachment.

1. Create in Table1

``````Tag =
VAR _name2 =
CALCULATE (
MAX ( 'Table2'[Name] ),
FILTER ( 'Table2', 'Table2'[Code] = 'Table1'[Code] )
)
RETURN
IF ( _name2 = 'Table1'[Name], 1, 0 )``````

Tag in Table1

2. Create in Table2

``````Tag =
VAR _name1 =
CALCULATE (
MAX ( 'Table1'[Name] ),
FILTER ( 'Table1', 'Table1'[Code] = 'Table2'[Code] )
)
RETURN
IF ( _name1 = 'Table2'[Name], 1, 0 )``````

Tag in Table2

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support

Hi @Anonymous ,

You can create the calculated columns as below in Table1 and Table2 separately, please find the details in the attachment.

1. Create in Table1

``````Tag =
VAR _name2 =
CALCULATE (
MAX ( 'Table2'[Name] ),
FILTER ( 'Table2', 'Table2'[Code] = 'Table1'[Code] )
)
RETURN
IF ( _name2 = 'Table1'[Name], 1, 0 )``````

Tag in Table1

2. Create in Table2

``````Tag =
VAR _name1 =
CALCULATE (
MAX ( 'Table1'[Name] ),
FILTER ( 'Table1', 'Table1'[Code] = 'Table2'[Code] )
)
RETURN
IF ( _name1 = 'Table2'[Name], 1, 0 )``````

Tag in Table2

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

@Anonymous , New column in Table 1

Tag = if( [Name] = max(filter(Table2, Table2[Code] = Table1[Code]), Table2[Name] ) , 1, 0)

New column in Table 2
Tag = if( [Name] = max(filter(Table1, Table2[Code] = Table1[Code]), Table1[Name] ) , 1, 0)

Anonymous
Not applicable

Hmmm it won't work

For some reason it cannot detect the Name column in table2

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors