Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
If you need them as a column you can use this, create a new column with the following DAX
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.
If you need them as a column you can use this, create a new column with the following DAX
Try this:
Create one calculated measure to get preference as true or false, based on count.
Use that calculated measure in a matrix visual.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |