One slicer from multiple fields

[mforest]

Hello! 

I am having issues with putting multiple fields into one slicer. I am working with what is essentially appointment survey data (see table example below). In the survey, we have a few questions that are "select all that apply." The data is then separated into what is essentially 0/1 columns. I'd like to create a slicer that would include Activity 1, Activity 2, Activity 3, etc. so that I can get count of who and how many people participated in each activity.

I have looked at Hierarchy Slicers (which doesn't really work since there isn't a hierarchy to the activities - but i feel like this might be a solution for me?) and have tried unpivoting, but since I have several columns that were transformed into 0/1 due to a select all the apply option, that doesnt seem to really work either. 

Open to trying anything! Thanks!

Visit #	Person ID	ACTIVITY 1 COMPLETED	ACTIVITY 2 COMPLETED	ACTVITIY 3 COMPLETED
Visit 1	Person 1	Activity 1	Activity 2
Visit 2	Person 1			Activity 3
Visit 1	Person 2		Activity 2	Activity 3
Visit 1	Person 3	Activity 1
Visit 2	Person 3		Activity 2
Visit 1	Person 4	Activity 1		Activity 3

[mforest]

Thank you all! It looks like unpivot is the way to go. My dataset has multiple "select all that apply" questions that transformed to 0/1, so I was concerned how the unpivot would treat my other 0/1 columns (and if there was a better way). Since y'all are confident that unpivot is the way to go, I simplified my table and I think I figured out how I can make it work. I'm more comfortable working with wide data than long, so the unpivot is just something I need to get used to, and figure out how to use it. 

Thank you all for your replies! Much appreciated!

[v-rzhou-msft · Accepted solution]

Hi @mforest 

Just like lbendlin and amitchandak replied before, we can solve this problem by Unpivot and Measure.

Firstly we need to use unpivot to change our data model.

Two ways:

1.    Unpivot column in Power Query Editor:

Open Power Query Editor —— Select 3 Columns as below —— Use Unpivot Columns in Transform Tab —— Delete Attribute Column.

Result：

2.    Use DAX：

Create New table in Modeling Tab:

Unpivot By DAX = UNION(
            SELECTCOLUMNS('Table',"visit#",'Table'[Visit#],"PersonalID#",'Table'[Person ID],"Activity#",'Table'[ACTIVITY 1 COMPLETED]),
            SELECTCOLUMNS('Table',"visit#",'Table'[Visit#],"PersonalID#",'Table'[Person ID],"Activity#",'Table'[ACTIVITY 2 COMPLETED]),
            SELECTCOLUMNS('Table',"visit#",'Table'[Visit#],"PersonalID#",'Table'[Person ID],"Activity#",'Table'[ACTVITIY 3 COMPLETED]))

Result:

Then we need a Slicer to filter Activity：

Create a new table like above and use it to build a Slicer.

Slicer = DISTINCT('Unpivot in PowerQueryEditor'[Value])

Result:

Then we create two measure. Add Measure1 into Table(Unpivot in PowerQueryEditor) and use Measure2 to create a Card. We can see the number of people in Card.

Measure1:

Measure1 =

VAR _a = VALUES(Slicer[Value])

return

CALCULATE(MAX('Unpivot in PowerQueryEditor'[Person ID]),FILTER('Unpivot in PowerQueryEditor','Unpivot in PowerQueryEditor'[Value] in _a))

Measure2:

Measure 2 =

VAR _a = VALUES(Slicer[Value])

return

CALCULATE(COUNT('Unpivot in PowerQueryEditor'[Person ID]),FILTER('Unpivot in PowerQueryEditor','Unpivot in PowerQueryEditor'[Value] in _a))

All is done , let’s select Activity1 to have a test.

Result:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EXb0fbEc0WVDt0jt...

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@mforest , One way is unpivot

Second is that you create a common dimension from these three columns

ore create a new table like

Activity = distinct(union(distinct(Table[Activity1]),distinct(Table[Activity2]),distinct(Table[Activity3])))

and join with all three activity and then use userelation to activate the join

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[lbendlin]

Yes, unpivoting is the way to go. Describe where you got stuck trying that.

You will want to end up with a table 

VISIT #  Person #  Activity #