Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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")
 
Screenshot_1.png
 
If this solves your issue please mark this as your accepted solution and happy coding 🙂
 

View solution in original post

5 REPLIES 5
tarunsingla
Solution Sage
Solution Sage
Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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")
 
Screenshot_1.png
 
If this solves your issue please mark this as your accepted solution and happy coding 🙂
 

Try this:

 

Create one calculated measure to get preference as true or false, based on count.

Use that calculated measure in a matrix visual.

Untitled.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.