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
Anonymous
Not applicable

Tagging rows in power bi with a calculated column

Table1

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

 

Table2

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

 

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

CodeNameTag
123Apple1
124Orange1
125Tomato0
126Grapes0
127Pineapple1
128Melon0

 

Table2

CodeNameTag
123Apple1
124Orange1
125Banana0
126Spinach0
127Pineapple1
128Garlic0

 

How do I achieve this?

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
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 Table1Tag 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 Table2Tag 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.

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
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 Table1Tag 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 Table2Tag 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.
amitchandak
Super User
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

@amitchandak 

Hmmm it won't work

Iver199_0-1639557217523.png

For some reason it cannot detect the Name column in table2

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.