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

     
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