cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinDoh1
Post Patron
Post Patron

How to create a slicer based on column value (now I need to link measure (logic) to a slicer table)

I am opening up a new post because I have not gotten a solution from my original post last week.

I was able to get some feedbacks on the approach (as far as Measure is concerned), but need some advice on how to link a table (for a slicer selection value) with a measure (logic).

 

I am sharing Pbix file here.

 

I am trying to have a slicer based on the value of columns (DT, OT and Total).

JustinDoh1_0-1632784419572.png

 

I am trying to have a slicer that changes a Line graph based on selection of DT, OT and Total.

 

There are three columns (DT Col, OT Col and Total Col).

I also have a three measures (DT, OT and Total).

 

I created a table called 'Slicer" (to be used as a slicer).

JustinDoh1_1-1632784617789.png

I honestly am not sure this is a way to create a table as it is a very simple table.

 

I created two measures that have a logic of selection of a slicer.

I created two different versions (one with IF and the other one with Switch), but both should have a same logic.
JustinDoh1_2-1632784689543.png

 

JustinDoh1_3-1632784732506.png

 

My question now is how to link the table (that has a simple output of choice) with the logic?

 

Thanks for help.

 

 

 

 

3 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

@JustinDoh1 

You would write a measure that reads the slection of the slicer and returns the correct measure, like this.

Display Measure = 
VAR _Selection = SELECTEDVALUE ( Slicer[Selection] )
RETURN 
SWITCH ( 
    _Selection,
    "OT",[OT],
    "DT",[DT],
    "Total",[Total]
)

Then you put that measure in the values and the Slicer[Selection] column in the Legend of the chart.
I have updated the attached file with the changes.

jdbuchanan71_0-1632788746370.png

 

View solution in original post

AllisonKennedy
Super User
Super User

@JustinDoh1 

 

Hmm, this is an interesting one. Do you want them to be able to select multiple choices at once or just one?

I'm not sure what your other post was, but this looks to me like data that could be unpivoted?

 

Do this in Power Query, so click 'Transform Data' to get there: 

 

AllisonKennedy_0-1632788156366.png

 

Create a 'title' measure with DAX: 

Title = CONCATENATEX(VALUES(vw_punch_variance_OT[Time Type]), vw_punch_variance_OT[Time Type], ", ") & " by Date"

 

Then you can use conditional formatting to update the date of the chart to match what the user selects: 

AllisonKennedy_1-1632791609960.png

 

AllisonKennedy_2-1632791675914.png

 

I wasn't sure if OT and DT are the only choices, but you can change the 'title' measure to say 'Total' or anything you want really, just need to know all the scenarios. 😀

 

See file below signature. To view what I've done in Power Query you'll have to 'Apply changes' and look at the vw_punch_variance_OT table only.

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

@JustinDoh1 Sorry for my delayed reply - are you still needing help with the unpivot totals? Generally with the 'total' we remove that column first, then Unpivot just the values. 

 

Think of Power BI like Excel - it works best with a database format table. You need to have one piece of info in each column and only one column per piece of info: so not two or three columns for hours like you currently have, but just one column for hours and one column for the type (OT vs DT). 

 

Just like you wouldn't include the OT, DT and Total in a graph in Excel (you'd pick either Totals or no Totals), same goes for Power BI: decide what level of granularity you want/need the report in. Use that only, and let the measures create the totals for you in the visuals. 

 

For your stacked bar chart, you wouldn't want OT, DT and Total: That would make everything look like double what it is. To get total, just select OT and DT in the slicer. If you want a 'Total' option in the slicer, we can make a workaround for that, but can also just clear the filter in slicer. 

 

I'm interested to hear how you're going, so keep in touch! 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

@JustinDoh1 

 

Hmm, this is an interesting one. Do you want them to be able to select multiple choices at once or just one?

I'm not sure what your other post was, but this looks to me like data that could be unpivoted?

 

Do this in Power Query, so click 'Transform Data' to get there: 

 

AllisonKennedy_0-1632788156366.png

 

Create a 'title' measure with DAX: 

Title = CONCATENATEX(VALUES(vw_punch_variance_OT[Time Type]), vw_punch_variance_OT[Time Type], ", ") & " by Date"

 

Then you can use conditional formatting to update the date of the chart to match what the user selects: 

AllisonKennedy_1-1632791609960.png

 

AllisonKennedy_2-1632791675914.png

 

I wasn't sure if OT and DT are the only choices, but you can change the 'title' measure to say 'Total' or anything you want really, just need to know all the scenarios. 😀

 

See file below signature. To view what I've done in Power Query you'll have to 'Apply changes' and look at the vw_punch_variance_OT table only.

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

@AllisonKennedy I am trying to understand how Unpivot works when it requires Total (OT + DT).

I was trying to follow the step of Unpivoting, but not quiet understand from your graphics. Also, I have a new requirement it would be stacked bar graph that shows three scenarios (OT, DT and Total (that shows OT and DT in different colors)). How can we unpivot Total since this is a calculation?

@JustinDoh1 Sorry for my delayed reply - are you still needing help with the unpivot totals? Generally with the 'total' we remove that column first, then Unpivot just the values. 

 

Think of Power BI like Excel - it works best with a database format table. You need to have one piece of info in each column and only one column per piece of info: so not two or three columns for hours like you currently have, but just one column for hours and one column for the type (OT vs DT). 

 

Just like you wouldn't include the OT, DT and Total in a graph in Excel (you'd pick either Totals or no Totals), same goes for Power BI: decide what level of granularity you want/need the report in. Use that only, and let the measures create the totals for you in the visuals. 

 

For your stacked bar chart, you wouldn't want OT, DT and Total: That would make everything look like double what it is. To get total, just select OT and DT in the slicer. If you want a 'Total' option in the slicer, we can make a workaround for that, but can also just clear the filter in slicer. 

 

I'm interested to hear how you're going, so keep in touch! 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

@AllisonKennedy Actually, I ended up using unpivot, and everything is working. First when somebody suggested 'unpivot', I thought he was referring to Excel, not PowerBI. I tried using other way that other person indicated (I thought that approach is more dynamic), but I got stuck creating a slicer for matrix visual together. So, I ended up creating a calculated value of DT (dt1 + dt2) and OT (ot1 + ot2) from SQL, and just imported those two columns and unpivoted in Power BI. Thanks! Keep in touch. I am getting deeper into Power BI now. 🙂

Enjoy it! Yes, you'll be MUCH happier with the Unpivot. It's actually more dynamic and flexible than the other way, even though it seems longer/difficult at first. 

 

Power BI was born in Excel, so the Power Query/Power Pivot in Excel are the same tools we use in Power BI, and it's pretty easy to convert from one to the other.


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy Thank you so much for your help. Now, I realized that Power Query is available in PowerBI. When somebody mentioned about Power Query, I was thinking about modifying in Excel. 🙂

jdbuchanan71
Super User
Super User

@JustinDoh1 

You would write a measure that reads the slection of the slicer and returns the correct measure, like this.

Display Measure = 
VAR _Selection = SELECTEDVALUE ( Slicer[Selection] )
RETURN 
SWITCH ( 
    _Selection,
    "OT",[OT],
    "DT",[DT],
    "Total",[Total]
)

Then you put that measure in the values and the Slicer[Selection] column in the Legend of the chart.
I have updated the attached file with the changes.

jdbuchanan71_0-1632788746370.png

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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