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

[JustinDoh1]

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

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

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.

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

Thanks for help.

[jdbuchanan71 · Accepted solution]

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

[AllisonKennedy · Accepted solution]

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

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: 

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.

[AllisonKennedy · Accepted solution]

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

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: 

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.

[JustinDoh1]

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

[AllisonKennedy · Accepted solution]

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

[JustinDoh1]

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

[AllisonKennedy]

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.

[JustinDoh1]

@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 · Accepted solution]

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