Create Drill Through table from SWITCH Measure

Question

Hello,I have begun using the Switch function in conjunction with Slicers to create some bar chart visuals.&nbsp; Given the following Measure:&nbsp;MissingPunchCount_Unpivot_Switch = SWITCH(
                                        TRUE(),
                                        [Selected_MissingPunchPeriod] = "Yesterday", [MissingPunchCount_Unpivot_Yesterday],
                                        [Selected_MissingPunchPeriod] = "Current Pay Period", 
                                            CALCULATE( [MissingPunchCount_Unpivot], 
                                                            vw_RPT_MissedPunches_ALL__DALY[PayPeriod] = "Current" ),
                                        [Selected_MissingPunchPeriod] = "Previous Pay Period",
                                            CALCULATE( [MissingPunchCount_Unpivot], 
                                                            vw_RPT_MissedPunches_ALL__DALY[PayPeriod] = "Previous" ))&nbsp;In a Slicer, User can select from 3 time periods - Yesterday, Current Pay Period, and Previous Pay Period.&nbsp;Example of bar chart for "Yesterday" follows where the blurred out y-axis is Facility:&nbsp;This is all fine and good.&nbsp; Now I provide the User with a Drill Through button to navigate to a Details page containing a Table Visual at the Facility Level.&nbsp;However, this table is capturing all Rows for the Facility , and does not consider the time period selected.The issue I believe is the Drill Through does not recognize the various filters being used in my Switch Statement above.Hoping someone can provide some assistance or guidance as to how best to filter the Drill Through table to capture only the relevant rows derived from the Switch Statement.&nbsp;&nbsp;As always thanks in advance and kind regards, &nbsp;

rsbin · Answer

TheoC&nbsp;Thank you for the response.&nbsp; It is quite late where I am, so will wait till morning to dig into the links you provided.&nbsp;Thanks again and will reply with further details.&nbsp;

theoc · Answer

Hi&nbsp;rsbin&nbsp;&nbsp;I think you may need to adjust the measure and consider variables. All the results I have seen that drill down to keep filters, etc., incorporate them.&nbsp;&nbsp;&nbsp;A couple of them include:&nbsp;https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=b52f0351-c430-43d2-8665-9bee835416c7&amp;CommunityKey=3a98540b-3655-4795-b8cb-30dbeb93ba78&amp;tab=digestviewerBoth incorporate SELECTEDVALUE as a variable which I believe is going to be a key component of getting what you want.&nbsp;&nbsp;I was going to recreate your measure, however, I couldn't really get a full understanding of the table / field naming conventions, or of the existing measures.&nbsp;Hope this helps 🙂&nbsp;Theo

theoc · Answer

Sounds good mate! Have a lovely evening 🙂&nbsp;

rsbin · Answer

Good Morning,TheoCand Others,I have read through the links provided, but am unable to see how they relate to my situation here.&nbsp; I will simplify my original code to make it easier to understand:&nbsp;Count Missing Punches = SWITCH(
          TRUE(),
     Slicer Selection = "Yesterday", Count Missing Punches for Yesterday,
     Slicer Selection = "Current Pay Period", Count Missing Punches for Current Pay Period
     Slicer Selection = "Previous Pay Period", Count Missing Punches for Previous Pay Period&nbsp;If "Yesterday" is the Slicer Selection, this results in the bar chart shown in orgininal post:Facility 1 has 12 Missing Punches.&nbsp; I am providing the User the ability to Drill Through to a page containing a table in order to view these 12 rows.&nbsp; The Drill Through page recognizes Facility 1 filter from the Bar Chart, but does not recognize the filter context "Yesterday" as it is embedded in another Measure.&nbsp; Instead of returning 12 rows, I get all rows for Facility 1.&nbsp;The only way I can think of resolving this is to create 3 different Drill Through pages and use Conditional Navigation depending on the Slicer Selection.&nbsp; I was hoping there might be a better solution.TheoC&nbsp; Appreciate any further thoughts or other ideas from the Community.Thanks again and regards,

Forum Discussion

Create Drill Through table from SWITCH Measure

4 Replies

Related content

Need help creating a measure

Help to create DAX Measure

Create count measure

Creating new measure, only showing other measures

Create Measure to Return Highest Value