Forum Discussion
Creating a column that returns True/False based on values from a column in another table
Hello,
So I have 2 tables that look similar to this (but much longer):
Table A
FirstName LastName ID
Jim A 12345
John B 23456
Bob F 34567
Bill R 98765
Table B
FirstName LastName ID Preferences
Jim A 12345 Blue Raspberry
Jim A 12345 Fruit Punch
Jim A 12345 Vanilla
John B 23456 Fruit Punch
John B 23456 Orange
Bob F 34567 Blue Raspberry
Bob F 34567 Orange
Bob F 34567 Vanilla
Bill R 98765 Fruit Punch
Bill R 98765 Lemon Lime
What I want to do is add another column to Table A titled "Blue Raspberry" that returns "True" or "False" based on whether or not each ID has a preference for "Blue Raspberry" or not. Ideally, it would look something like this:
Table A (Updated)
FirstName LastName ID Blue Raspberry
Jim A 12345 True
John B 23456 False
Bob F 34567 True
Bill R 98765 False
Eventually, I will want to do a column for each preference, but I figured that if I knew how to do it for one, I could do it for the rest.
If you need them as a column you can use this, create a new column with the following DAX
Blue Raspberry = CONTAINS(FILTER(Table_B,Table_A[ID]=Table_B[ID]),Table_B[Preferences] , "Blue Raspberry")then update the title and reference for each preference:Fruit Punch = CONTAINS(FILTER(Table_B,Table_A[ID]=Table_B[ID]),Table_B[Preferences] , "Fruit Punch")Orange = CONTAINS(FILTER(Table_B,Table_A[ID]=Table_B[ID]),Table_B[Preferences] , "Orange")Vanilla = CONTAINS(FILTER(Table_B,Table_A[ID]=Table_B[ID]),Table_B[Preferences] , "Vanilla")Lemon Lime = CONTAINS(FILTER(Table_B,Table_A[ID]=Table_B[ID]),Table_B[Preferences] , "Lemon Lime")If this solves your issue please mark this as your accepted solution and happy coding :)
5 Replies
That seems like it could be a step in the right direction, but it does not completely solve my issue. If I power Pivot in the table which already has the preferences, then it does not collapse the rows, and each person still has as many rows as they did originally. It also does not exactly give me an option to return True or False, based on what I have seen. I need my end result to give me one row per person, as well as a "True" or "False for each preference
Try this:
Create one calculated measure to get preference as true or false, based on count.
Use that calculated measure in a matrix visual.
