Forum Discussion
Creating a column that returns True/False based on values from a column in another table
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 :)
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.
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 :)This initially seems really easy to do, but I'm not sure there's a good way to accomplish it.
If you've only got 5 options, it's pretty easy to do each one individually as a DAX measure or calculated column. However, if you have a large amount of possible preferences, I don't know of a way to automatically create a column with the name of each DISTINCT preference value.
To get you started though, you could create a summary table like this with DAX:
TrueFalseTable = SUMMARIZE(Table2,
Table2[ID], Table1[First], Table1[Last],
"Blue Raspberry", CONTAINS(DISTINCT(Table2[Preference]),Table2[Preference], "Blue Raspberry")
//Add a column for each Preference
)Just be sure to do this with the New Table options under the Modeling tab in Report View.
If anybody can shed some light on a way to determine all of the distinct preference options, put them into a single row, promote the preferences to headers, AND set up a custom function for those columns to check if each person liked the thing, I would be very interested in learning how.
