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.
I need to write a DAX forumula that will create a New Column in Table A based on the follow criteria:
IF Table A [Org Name] is found in either Table B [Org Name 1], Table B [Org Name 2], Table B [Org Name 3] or Table B [Org Name 4]
AND
If Table A [Training Title] is found in either Table B [Training Title Group 1] or Table B [Training Title Group 2]
I completed New Column based on the IF and AND statements and represents the desired outcome.
(Please note if table joins are necessary) Thanks so much in advance
Table A | |||||
Org Name | Training Title | New Column | |||
ORG CDS | Course B | Yes | |||
ORG ABC | Course C | No | |||
ORG ATF | Course B | Yes | |||
ORG RSD | Course G | No | |||
Org AB | Course C | Yes | |||
Table B | |||||
Org Name 1 | Org Name 2 | Org Name 3 | Org Name 4 | Training Title Group 1 | Training Title Group 2 |
Org AB | ORG CDS | Org AB | ORG ATF | Course A | Course B |
Org AB | ORG ATF | ORG ATF | ORG RSD | Course B | Course C |
Org AB | ORG RSD | ORG RSD | Org AB | Course C | Course B |
Org AB | ORG ATF | Org AB | ORG RSD | Course B | Course A |
Org AB | ORG ATF | ORG ATF | ORG ATF | Course A | Course C |
Solved! Go to Solution.
Hi @Stadeo
How about this:
Column_Test = IF ( (TableA[Org Name] IN VALUES ( TableB[Org Name 1] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 2] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 3] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 4] )) && (TableA[Training Title] IN VALUES ( TableB[Training Title Group 1] ) || TableA[Training Title] IN VALUES ( TableB[Training Title Group 2] )); "Yes"; "No" )
Thank you so much. This worked. Awesome!!!! I spend quite some time on this and I really wasn't getting anywhere. What a great way to end a Friday.
Steve
Hi @Stadeo
How about this:
Column_Test = IF ( (TableA[Org Name] IN VALUES ( TableB[Org Name 1] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 2] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 3] ) || TableA[Org Name] IN VALUES ( TableB[ Org Name 4] )) && (TableA[Training Title] IN VALUES ( TableB[Training Title Group 1] ) || TableA[Training Title] IN VALUES ( TableB[Training Title Group 2] )); "Yes"; "No" )
Thank you so much. This worked. Awesome!!!! I spend quite some time on this and I really wasn't getting anywhere. What a great way to end a Friday.
Steve
Or another version, perhaps more readable:
New_Column_v2 = VAR _Condition1 = TableA[Org Name] IN UNION ( VALUES ( TableB[Org Name 1] ); VALUES ( TableB[ Org Name 2] ); VALUES ( TableB[ Org Name 3] ); VALUES ( TableB[ Org Name 4] ) ) VAR _Condition2 = TableA[Training Title] IN UNION ( VALUES ( TableB[Training Title Group 1] ); VALUES ( TableB[Training Title Group 1] ) ) RETURN IF ( _Condition1 && _Condition2; "Yes"; "No" )
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |