Need to create drilldown for data within a time period

Power Query Data Setup
11

@kjssdca,

Okay, attached is a “proof of concept” mockup. I’m not sure these are exactly the correct slicer configurations, but they should illustrate the points. There are a lot of fiddly little steps in this process, but none particularly difficult. Here’s how I built the mockup:

  1. Added a date slicer based on Start of Wk Display field, and a list slicer based on Contract Status to the Weekly Sales page

  2. Copied each of the slicers to the Closed Contract Detail page. Selected “Sync” in each case when I pasted to the destination page:

image
image1050×288 10.1 KB

  1. Added a blank button to the Weekly Sales page, next to the slicers and set the action for that button to “Page Navigation”

  2. Created three very similar measures to control a) the page navigation destination page; b) the blank button text; and c) the blank button background color. Here’s the measure for the page navigation destination page – the others are almost identical, except for the values they return at the end if the statement is true versus false. The statement evaluated triggers true if any of the “confirmed” contract status options is selected in the list slicer, false otherwise.

    Page Nav Button =

    SWITCH(TRUE(),
    CONTAINSSTRING(
        CONCATENATEX(
            VALUES(Potentials_001[Contract Status] ),
            Potentials_001[Contract Status],
            ",",
            Potentials_001[Contract Status],
            ASC
        ),
        "Confirmed"
    ),        
    "Closed Sales Detail",
    "Weekly Sales"
)

  3. Then set the page destination, button text and button background conditional options based on the measures created above.

  4. At that point, we now have the button configured to look like this if one or more of the “confirmed” status options are selected and set to drill through to the Closed Contract Details page:

image

And configured to look like this if only the “Sent Waiting for Sign” option is selected:

image

In addition, in the latter case page navigation destination is set to the current page, so even if the user clicks on the button when it is grayed out, nothing will happen.

  1. Finally, on the Closed Contract Details page, I added another page navigation button - this one a “back” button returning the user to the Weekly Sales page

I hope this is helpful to you. Full solution file attached so you can delve into the details of the other measures and look at all the configuration options necessary to make this work. If you use a nice button template and good conditional background and font color options, you can create a really good-looking, smooth user experience.

2 Likes