Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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" )
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |