cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 IDACTIVITY 1 COMPLETEDACTIVITY 2 COMPLETEDACTVITIY 3 COMPLETED
Visit 1Person 1Activity 1Activity 2 
Visit 2Person 1   Activity 3
Visit 1Person 2 Activity 2Activity 3
Visit 1Person 3Activity 1  
Visit 2Person 3 Activity 2 
Visit 1Person 4Activity 1 Activity 3

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @mforest 

Just like lbendlin and 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.

1.png

Result

2.png

2.    Use DAX

Create New table in Modeling Tab:

3.png

 

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:

4.png

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:

5.png

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 , lets select Activity1 to have a test.

Result:

6.png

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. 

View solution in original post

4 REPLIES 4
Frequent Visitor

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!

Community Support
Community Support

Hi @mforest 

Just like lbendlin and 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.

1.png

Result

2.png

2.    Use DAX

Create New table in Modeling Tab:

3.png

 

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:

4.png

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:

5.png

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 , lets select Activity1 to have a test.

Result:

6.png

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. 

View solution in original post

Super User IV
Super User IV

@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...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User III
Super User III

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 #

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors