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

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

     

    • Saffy's avatar
      Saffy
      New Member
      9 months ago

      Do I have to do that for each calculation , like for each metric? Each measure or can that be applied to the filter
      I have a few, net sales, margin, count orders, count of products etc..

      av