Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 identifier. All measure have been consolidated into another table for easier reference.
Help Required - I am using a date slicer from the "Date" table, which currently has the option to select "From" & "To" dates (between option); however what I am looking for is the "From" date to be disabled and users get to select only from the "To" date and basis this selection, the "From" date should substract 28 days/4 weeks/n dates (though the "From" date should be disabled, it should still show the "From" date)
I've searched the community however not able to see one on this topic (maybe am using the wrong keywords).
Any help on suggestions/solutions would be greatly appreciated
Edit - Forgot to mention, I am trying to achieve this in a "Table Matrix". I was able to work around this on the chart visuals.
Solved! Go to Solution.
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
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
Thank you, @v-yangliu-msft , 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%B1...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |