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.
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?
Solved! Go to Solution.
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 )
2. Create in Table2
Tag =
VAR _name1 =
CALCULATE (
MAX ( 'Table1'[Name] ),
FILTER ( 'Table1', 'Table1'[Code] = 'Table2'[Code] )
)
RETURN
IF ( _name1 = 'Table2'[Name], 1, 0 )
Best Regards
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 )
2. Create in Table2
Tag =
VAR _name1 =
CALCULATE (
MAX ( 'Table1'[Name] ),
FILTER ( 'Table1', 'Table1'[Code] = 'Table2'[Code] )
)
RETURN
IF ( _name1 = 'Table2'[Name], 1, 0 )
Best Regards
@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)
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |