Forum Discussion
One slicer from multiple fields
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 |
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:
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.
4 Replies
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 #
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
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:
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.
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!
