Dynamic Filtering

[PowerBI123456]

Hi, I am trying to see how many requests were put in for responses received in the last 7 days. However, the request could have happned before the 7 day window and I dont want to filter those out. Anyway to do a dynamic filter to include all requests that are before the last 7 days a response was received?

[danextian · Accepted solution]

Hi @PowerBI123456 ,

I'm hoping I get what you to achieve.
There are two approaches I can think of.

One - create a calculated column to get the response date for each account and then use this column in  a date slicer for the last 7 days.

Days Count - Response Date to Selected Date =
VAR __RESPONSE_DATE =
    SELECTEDVALUE (
        'Dates (disconnected)'[Date],
        MAX ( 'Dates (disconnected)'[Date] )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'Fact'[Date] ), __RESPONSE_DATE, DAY )

Two -  This one is more dynamic. Create a disconnected (no relationship to Fact) calendar table and several meausres to compute for the number of days from response to the selected date in the disconnected calendar table.

Dates (disconnected) = 
CALENDAR ( MIN ( 'Fact'[Date] ), MAX ( 'Fact'[Date] ) )

Response Date (Measure) = 
CALCULATE (
    MAX ( 'Fact'[Date] ),
    ALLEXCEPT ( 'Fact', 'Fact'[Account] ),
    'Fact'[Action] = "Response"
)

Days Count - SELECTED Date to Selected Date = 
VAR __SELECTED_DATE =
    SELECTEDVALUE (
        'Dates (disconnected)'[Date],
        MAX ( 'Dates (disconnected)'[Date] )
    )
RETURN
    DATEDIFF ( [Response Date (Measure)], __SELECTED_DATE, DAY )

For reference, refer to the sample pbix: https://drive.google.com/file/d/1hvUNGahLeo7TyZNvkNU1LNsDRi2acTEC/view?usp=sharing

[parry2k]

@PowerBI123456 sorry it is not clear to me at all, I'm sure it's me. I hope someone else can help who understood what you are looking for.

[PowerBI123456]

@parry2k No its okay, I am not explaining it well. What I am trying to solve is: For any responses received in the last 7 days, when were the requests put in and by who. So in my example, accounts 2 and 3 had a response within the last 7 days (2/1/21 for account 2 and 2/2/21 for account 3), so the requests would have been on 11/1/20 by Kate (account 2) and 1/3/21 by Jim (account 3). Account 1 wouldn't apply because its response happened more than 7 days ago. I can't add a page filter on the date because then I am filtering out all the requests since they happened more than 7 days ago. Does that help?

[parry2k]

@PowerBI123456 not fully clear what you are looking for?

[PowerBI123456]

@parry2k I want to determine when requests were put in, but only for responses in the last 7 days. So in the example below, I only care for accounts 2 and 3 since the response was in the last 7 days. When I add a date filter on the page, I will get the responses that happened in the 7 last days but will miss the corresponding requests since they were before 7 days. Does that make sense?

[danextian · Accepted solution]

Hi @PowerBI123456 ,

I'm hoping I get what you to achieve.
There are two approaches I can think of.

One - create a calculated column to get the response date for each account and then use this column in  a date slicer for the last 7 days.

Days Count - Response Date to Selected Date =
VAR __RESPONSE_DATE =
    SELECTEDVALUE (
        'Dates (disconnected)'[Date],
        MAX ( 'Dates (disconnected)'[Date] )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'Fact'[Date] ), __RESPONSE_DATE, DAY )

Two -  This one is more dynamic. Create a disconnected (no relationship to Fact) calendar table and several meausres to compute for the number of days from response to the selected date in the disconnected calendar table.

Dates (disconnected) = 
CALENDAR ( MIN ( 'Fact'[Date] ), MAX ( 'Fact'[Date] ) )

Response Date (Measure) = 
CALCULATE (
    MAX ( 'Fact'[Date] ),
    ALLEXCEPT ( 'Fact', 'Fact'[Account] ),
    'Fact'[Action] = "Response"
)

Days Count - SELECTED Date to Selected Date = 
VAR __SELECTED_DATE =
    SELECTEDVALUE (
        'Dates (disconnected)'[Date],
        MAX ( 'Dates (disconnected)'[Date] )
    )
RETURN
    DATEDIFF ( [Response Date (Measure)], __SELECTED_DATE, DAY )

For reference, refer to the sample pbix: https://drive.google.com/file/d/1hvUNGahLeo7TyZNvkNU1LNsDRi2acTEC/view?usp=sharing

[PowerBI123456 · Accepted solution]

Thank you! I had to tweak option 2 a little but it worked!