Forum Discussion

ekodami's avatar
ekodami
Icon for Helper I rankHelper I
4 months ago
Solved

Selecting a slicer source field based on filtered value

Hi folks, I want to give end users the option to select which field to base their filter from within a slicer. All fields are date fields (Submitted Date, Scheduled Start Date, Planned Completion Date, Completed Date, Closed Date...). Therefore, if the end user wants to see visuals results based on the 'Submitted Date', I want the Slicer to load this field and allow selecting date or a range of date from it. Hope you all get it.

So what I did was:

  1. Create a table within Power BI Desktop and listed all fields subjects to filtering.
  2. Then created a dropdown slicer listing those fields
  3. Create a second slicer pointing to the fact table field (ex. Submitted Date)

Now I am wondering, should I simply create a bookmark for every single filter option or there is some DAX coding I could put down to relate both slicers and have the second responding to the choice made within the first?

I have pasted a pics of my 2 slicers for a visual explanation.

Pls advise,

Thank you in advance

Tips and Tricks
  • cengizhanarslan's avatar
    cengizhanarslan
    4 months ago

    Step 1) Create your field selector table 

    DateFieldSelector =
DATATABLE (
    "FieldName", STRING,
    {
        { "Submitted Date"       },
        { "Scheduled Start Date" },
        { "Plan End Date"        },
        { "Actual Start Date"    },
        { "Actual End Date"      },
        { "Last Modified Date"   }
    }
)

     

    Step 2) Create a single date parameter table for the range slicer

    Use a What-if parameter or a date table as your second slicer source β€” this gives users a date range picker disconnected from any specific column.

     

    Step 3) Create the dynamic filter measure

    Dynamic Date Filter =
VAR _SelectedField =
    SELECTEDVALUE ( DateFieldSelector[FieldName] )
VAR _StartDate =
    MIN ( 'DateRange'[Date] )
VAR _EndDate =
    MAX ( 'DateRange'[Date] )
VAR _SelectedDate =
    SWITCH (
        _SelectedField,
        "Submitted Date",        MAX ( FactTable[Submitted Date] ),
        "Scheduled Start Date",  MAX ( FactTable[Scheduled Start Date] ),
        "Plan End Date",         MAX ( FactTable[Plan End Date] ),
        "Actual Start Date",     MAX ( FactTable[Actual Start Date] ),
        "Actual End Date",       MAX ( FactTable[Actual End Date] ),
        "Last Modified Date",    MAX ( FactTable[Last Modified Date] ),
        BLANK ()
    )
RETURN
    IF (
        _SelectedDate >= _StartDate && _SelectedDate <= _EndDate,
        1,
        BLANK ()
    )

     

    Step 4) Apply as a visual-level filter

    Add Dynamic Date Filter to Filters on this visual β†’ set to is 1 on each visual that needs to respond.

8 Replies

    • ekodami's avatar
      ekodami
      Icon for Helper I rankHelper I
      4 months ago

      Hi, I tried this solution but it doesn't seem to work for the purpose of my need. I will try again and advise,

       

      Thanks

  • grazitti_sapna's avatar
    grazitti_sapna
    Icon for Super User rankSuper User
    4 months ago

    Hi ekodami,

     

    You can achieve it by creating a disconnected date table and then using filteration in a measure.

     

    I've created a sample .pbix file for you with working soltuion. Kindly take a look to attached file.

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    πŸ’‘ Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    πŸŽ– As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    πŸ”— Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    4 months ago

    Step 1) Create your field selector table 

    DateFieldSelector =
DATATABLE (
    "FieldName", STRING,
    {
        { "Submitted Date"       },
        { "Scheduled Start Date" },
        { "Plan End Date"        },
        { "Actual Start Date"    },
        { "Actual End Date"      },
        { "Last Modified Date"   }
    }
)

     

    Step 2) Create a single date parameter table for the range slicer

    Use a What-if parameter or a date table as your second slicer source β€” this gives users a date range picker disconnected from any specific column.

     

    Step 3) Create the dynamic filter measure

    Dynamic Date Filter =
VAR _SelectedField =
    SELECTEDVALUE ( DateFieldSelector[FieldName] )
VAR _StartDate =
    MIN ( 'DateRange'[Date] )
VAR _EndDate =
    MAX ( 'DateRange'[Date] )
VAR _SelectedDate =
    SWITCH (
        _SelectedField,
        "Submitted Date",        MAX ( FactTable[Submitted Date] ),
        "Scheduled Start Date",  MAX ( FactTable[Scheduled Start Date] ),
        "Plan End Date",         MAX ( FactTable[Plan End Date] ),
        "Actual Start Date",     MAX ( FactTable[Actual Start Date] ),
        "Actual End Date",       MAX ( FactTable[Actual End Date] ),
        "Last Modified Date",    MAX ( FactTable[Last Modified Date] ),
        BLANK ()
    )
RETURN
    IF (
        _SelectedDate >= _StartDate && _SelectedDate <= _EndDate,
        1,
        BLANK ()
    )

     

    Step 4) Apply as a visual-level filter

    Add Dynamic Date Filter to Filters on this visual β†’ set to is 1 on each visual that needs to respond.

    • ekodami's avatar
      ekodami
      Icon for Helper I rankHelper I
      4 months ago

      Hi, thanks for the hint. I will test it and update

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 months ago

    Hi ekodami 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    Deku cengizhanarslan grazitti_sapna Thanks for the inputs.

    I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.