Forum Discussion
keep filter ( ) + filter ( all ( ) )
slicer:
Date[Date] = Oct 24, 2020 (saturday)
Report:
| Location | Product | Posted Price |
| Chicago | Apples | 1.25 |
| Chicago | Banana | 1.75 |
| Chicago | Carrots | 1.61 |
Produce Table:
| Location | Product | Posted Price | Date |
| Chicago | Apples | 1.25 | 10/23/20 |
| Chicago | Apples | 2.12 | 10/24/20 |
| Chicago | Apples | 2.51 | 10/25/20 |
| Chicago | Banana | 1.75 | 10/23/20 |
| Chicago | Banana | 2.65 | 10/24/20 |
| Chicago | Banana | 2.75 | 10/25/20 |
| Chicago | Carrots | 1.61 | 10/23/20 |
| Chicago | Carrots | 2.45 | 10/24/20 |
| Chicago | Carrots | 2.41 | 10/25/20 |
| Chicago | Mango | 0.75 | 10/23/20 |
posted price =
VAR WeekDays =
IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Day Short] ), "Mon" )
VAR SelDate =
IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Date] ), TODAY () )
VAR PostedPricesWeekday =
CALCULATE (
MIN ( 'Posted Prices'[Price] ),
'Posted Prices'[Price Date]
= IF ( HASONEVALUE ( Dates[Date] ), VALUES ( Dates[Date] ), TODAY () )
)
VAR PostedPricesSat =
CALCULATE (
MIN ( 'Posted Prices'[Price] ),
KEEPFILTERS (
FILTER (
ALL ( 'Posted Prices'[Price Date] ),
'Posted Prices'[Price Date] = SelDate - 1
)
)
)
VAR PostedPricesSun =
CALCULATE (
MIN ( 'Posted Prices'[Price] ),
KEEPFILTERS (
FILTER (
ALL ( 'Posted Prices'[Price Date] ),
'Posted Prices'[Price Date] = SelDate - 2
)
)
)
RETURN
IF (
WeekDays = "Sat",
PostedPricesSat,
IF ( WeekDays = "Sun", PostedPricesSun, PostedPricesWeekday )
)
There's a many to 1 relationship between produce table [date] and date [date].
Even though the slicer says 10/24/2020, I want to show the last available weekday price, which is 10/23/2020 (friday) in this case. I have included filter ( all ( ) ) in my measure, otherwise, the 10/24/2020 filter context would only return the saturday value. However, I would still like to keep the filter context for location & product because otherwise my measure would return 0.75 (from mangos). I tried using Keepfilters, but i'm not achieving my desired result.
Help?
5 Replies
eddd83 , Not very clear. You can have a date table with following columns
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)Measure =
var _max = maxx(allselected('Date'), 'Date'[Week Rank])
return
Calculate(min(Produce[Posted Price]) , filter(All('Date'), 'Date'[Week Rank]) =_max))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
sorry for the confusion. Please see the one drive link with my file:
Basically my measure (posted price) works for the weekdays, but returns a blank for the weekends. Ideally for the weekdays, it would show the previous friday (10/23) in this scenario.
Hi eddd83 ,
You can create a new weekday column in your Date table :
weekday = WEEKDAY(Dates[Date],2)Then use REMOVEFILTER to clear filter from slicer in [posted price]:
_posted price = var a = IF(SELECTEDVALUE(Dates[weekday])<=5,MAX(Dates[Date]),CALCULATE(MAX(Dates[Date]),FILTER(ALL(Dates),Dates[Date]<=MAX(Dates[Date])&&Dates[weekday]<=5))) return CALCULATE(MIN('Posted Prices'[Price]),'Posted Prices'[Price Date] = a,REMOVEFILTERS(Dates[Date]))For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUfmRSl0n3VLrP3tbkfEUg0Bg-YVEgmdGNbBs9Xb9iDDUg?e=BXBikd
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
