Forum Discussion

WYads's avatar
WYads
Regular Visitor
4 years ago
Solved

Dynamic Slicer basis user inputs on date

Hello Community - Need help with suggestions/solutions   I have a pbix with multiple tables (eg. sales, Hours, Volume, etc...) all linked to another "Date" table with the "dates" as a unique identi...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  WYads ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Date = DISTINCT('Table'[Date])

    day_table =
DISTINCT('Date'[Day])

    week_table =
DISTINCT('Date'[week])

    2. Create measure.

    Flag =
var _selectday=SELECTEDVALUE('day_table'[Day])
var _selectweek=SELECTEDVALUE('week_table'[week])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SWITCH(
    TRUE(),
 HASONEVALUE('day_table'[Day])&&NOT( HASONEVALUE('week_table'[week])),
IF(
MAX('Table'[Date])>=
DATE(YEAR(_max),MONTH(_max),DAY(_max)-_selectday)&&MAX('Table'[Date])   <=_max,1,0),
HASONEVALUE('week_table'[week]),
IF(
MAX('Table'[Date])>=
DATE(YEAR(_max),MONTH(_max),DAY(_max)- 7 *_selectweek)&&MAX('Table'[Date])   <=_max,1,0))

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

    When the Day slicer is 2, the date is displayed as 2022.12.29—2022.12.31

    When the Week slicer is 2, the date is displayed as 2022.12.17—2022.12.31

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  WYads ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Date = DISTINCT('Table'[Date])

day_table =
DISTINCT('Date'[Day])

week_table =
DISTINCT('Date'[week])

2. Create measure.

Flag =
var _selectday=SELECTEDVALUE('day_table'[Day])
var _selectweek=SELECTEDVALUE('week_table'[week])
var _max=MAXX(ALLSELECTED('Table'),[Date])
return
SWITCH(
    TRUE(),
 HASONEVALUE('day_table'[Day])&&NOT( HASONEVALUE('week_table'[week])),
IF(
MAX('Table'[Date])>=
DATE(YEAR(_max),MONTH(_max),DAY(_max)-_selectday)&&MAX('Table'[Date])   <=_max,1,0),
HASONEVALUE('week_table'[week]),
IF(
MAX('Table'[Date])>=
DATE(YEAR(_max),MONTH(_max),DAY(_max)- 7 *_selectweek)&&MAX('Table'[Date])   <=_max,1,0))

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

When the Day slicer is 2, the date is displayed as 2022.12.29—2022.12.31

When the Week slicer is 2, the date is displayed as 2022.12.17—2022.12.31

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

WYads's avatar
WYads
Regular Visitor
4 years ago

Thank you, Anonymous , for giving this a shot at! Much appreciated for taking your time to go through this and work out something.

 

Though not exactly what I was looking for, this did give me a workaround for another problem I was working on; hence accepting this as a solution.

 

For folks stuck on similar situations- You might want to look at this wonderful piece from Mustafa https://www.linkedin.com/pulse/custom-range-date-slicer-power-bi-dynamic-defaults-more-a%C5%9F%C4%B1ro%C4%9Flu