Forum Discussion

Saffy's avatar
Saffy
New Member
9 months ago
Solved

Dynamic Date Filter Toggles

i am very new to PBI, coming from a tableau background!

In tableau i could use a parameter field that would have two options: include and exclude

id then create an if condition, if inlcude then one date range, and if exclude then a different date range. I'd be able to add this a sheet filter.

I seem to be hitting a brick wall in delivering this in PBI. any guides/videos/tutorials or work around would be great

 

info about the data set

Key columns are [order_id], [order_date], [order_date_week]<truncated to monday, [order_date_week_num]

 

it seems to be failing because i want it as a column but this cant connect to a slicer to create that toggle function... any creative suggestions welcome 🙂

Need Help
Tips and Tricks
  • Saffy's avatar
    Saffy
    6 months ago

    i ended up creating the date/calendar table

    then creating this calculated column Is_L5W_Exc & Is_L5W_Inc as below


    Is_L5W_Exc =
    VAR TodayDate = TODAY()
    VAR StartOfCurrentWeek = TodayDate - WEEKDAY(TodayDate, 2) + 1
    VAR StartOfPeriod6 = StartOfCurrentWeek - 35
    RETURN
    IF(
        Calendar[Date] >= StartOfPeriod6 &&
        Calendar[Date] < StartOfCurrentWeek,
        TRUE(),
        FALSE()
    )

    then in my measures i did

    *Customer Count Last 5 Weeks =
    VAR Mode = SELECTEDVALUE('Selector - Timeframe'[Options])
    RETURN
    SWITCH(
        Mode,
        "Inc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Inc] = TRUE()),
        "Exc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Exc] = TRUE()),
        BLANK()
    )

     

11 Replies

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    9 months ago

    - Create a disconnected table with values like "Include" and "Exclude".
    - Use a slicer on that table to let users choose.
    - Write a DAX measure that checks the slicer selection and applies different date logic.
    - Filter visuals using that measure (e.g., [IsInDateRange] = TRUE).

    • Saffy's avatar
      Saffy
      New Member
      9 months ago

      It wouldn't let me create a measure from a column

      • MasonMA's avatar
        MasonMA
        Super User
        9 months ago

        Hi, 

        After you created a disconnected table with below logic, 

        Create a Measure, for example with below logic to show Past 3 days 'Request count' when user selects 'Include'. 

        IsInDateRange = 
VAR Selection = SELECTEDVALUE('ParameterTable'[Parameter], "Include")
RETURN
CALCULATE(
    COUNTROWS('Request'),
    FILTER(
        'Request',
        IF(
            Selection = "Include",
            'Request'[Created Date] >= TODAY()-3,
             'Request'[Created Date] < TODAY()-3
        )
    )
)

         

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    9 months ago

    Hi Saffy ,

    In each visual (e.g., net sales, margin, etc.), add a filter: IsInDateRange= True

    This way, the visual only shows data based on the selected toggle.

    You don’t need to recreate this for every metric. Just use this IsInDateRange measure as a filter across visuals. Your actual metrics (like Net Sales, Margin, etc.) stay untouched.

    Regards,
    Shruti

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    9 months ago

    Hi Saffy ,

    Just wanted to check if you got a chance to review the suggestion provided and whether that helped you address your query?

    Thank You

    • Saffy's avatar
      Saffy
      New Member
      9 months ago

      ok my table is called sales

      IsInDateRange =
      VAR Selection = SELECTEDVALUE('Include/Exclude'[Parameter], "Include")
      RETURN
      CALCULATE(
          COUNTROWS('Sales'),
          FILTER(
              'Sales',
              IF(
                  Selection = "Include",
                  'Sales'[order_date] >= TODAY()-3,
                   'Sales'[order_date] < TODAY()-3
              )
          )
      )

      so i've updated the formula and created it as a measure, but when i drag it onto a visual what do i filter these are the options

       



  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    7 months ago

    Hi Saffy ,

    Thanks for the update.
    It should ideally show the date range when selection is "Include". In the filter, you can select not blank and check if the value is showing correct number.

    Thank You.


    • Saffy's avatar
      Saffy
      New Member
      6 months ago

      i ended up creating the date/calendar table

      then creating this calculated column Is_L5W_Exc & Is_L5W_Inc as below


      Is_L5W_Exc =
      VAR TodayDate = TODAY()
      VAR StartOfCurrentWeek = TodayDate - WEEKDAY(TodayDate, 2) + 1
      VAR StartOfPeriod6 = StartOfCurrentWeek - 35
      RETURN
      IF(
          Calendar[Date] >= StartOfPeriod6 &&
          Calendar[Date] < StartOfCurrentWeek,
          TRUE(),
          FALSE()
      )

      then in my measures i did

      *Customer Count Last 5 Weeks =
      VAR Mode = SELECTEDVALUE('Selector - Timeframe'[Options])
      RETURN
      SWITCH(
          Mode,
          "Inc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Inc] = TRUE()),
          "Exc", CALCULATE(DISTINCTCOUNT('FACT_CALLS'[UniqueIdentifier]), Calendar[Is_L5W_Exc] = TRUE()),
          BLANK()
      )

       
  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    6 months ago

    Hi Saffy ,

    Thank you for the update. Glad that it worked it for you.
    If you face any challenges , feel free to reach out here. We would be happy to assist you.
    Thank you for being a part of Microsoft Community Forum.


    Regards,
    Shruti