Forum Discussion
Conditional Formatting -> Slicer
Hi everyone, I originally needed this code below (coupon or deal format) to apply conditional formatting when an item was on a deal/coupon depending on the date. However, I now need to modify this formula, with the same filters, to become a slicer based on yesterday's deals/coupons. The end goal is to create a metric that I can slice by coupon, deal, prime exclusive deal, lightning deal, etc. I've gotten close to creating this by the "coupon or deal column" formula below. However, this only slices the conditional formatting of the coupons/deals, not the rows. Thanks for your help!
Coupon or Deal Column =
VAR tempTable =
FILTER (
ALL('Calendar'),'Calendar'[DayRank]=MAX('Calendar'[DayRank])-1
)
VAR MiNIMUMDATE =
MINX ( tempTable, 'Deals and Coupons'[Coupon Start Date] )
VAR MAXIMUMDATE =
MAXX ( tempTable, 'Deals and Coupons'[Coupon End Date] )
VAR MiNIMUMDATE2 =
MINX ( tempTable, 'Deals and Coupons'[Deal Start Date] )
VAR MAXIMUMDATE2 =
MAXX ( tempTable, 'Deals and Coupons'[Deal End Date] )
VAR DATE2 =
MAXX ( tempTable, 'Deals and Coupons'[Lightning Deal Date] )
VAR MiNIMUMDATE3 =
MINX ( tempTable, 'Deals and Coupons'[Prime Exclusive Discount Start Date] )
VAR MAXIMUMDATE3 =
MAXX ( tempTable, 'Deals and Coupons'[Prime Exclusive Discount End Date] )
RETURN
IF (
AND (
[Yesterday] >= MiNIMUMDATE,
[Yesterday] <= MAXIMUMDATE
),
"Coupon",
IF (
AND (
[Yesterday] >= MiNIMUMDATE2,
[Yesterday] <= MAXIMUMDATE2
),
"7DD",
IF (
AND (
[Yesterday] >= MiNIMUMDATE3,
[Yesterday] <= MAXIMUMDATE3
),
"PED",
IF (
[Yesterday] = DATE2
,
"LD","None"
))))Coupon or Deal Format =
VAR tempTable =
FILTER (
'Deals and Coupons',
'Deals and Coupons'[Coupon Start Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
&& 'Deals and Coupons'[Coupon End Date] >= 'Deals and Coupons'[Coupon End Date]
&& 'Deals and Coupons'[Deal Start Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
&& 'Deals and Coupons'[Deal End Date] >= 'Deals and Coupons'[Deal End Date]
&& 'Deals and Coupons'[Prime Exclusive Discount Start Date] <= SELECTEDVALUE ( 'Calendar'[Date] )
&& 'Deals and Coupons'[Prime Exclusive Discount End Date] >= 'Deals and Coupons'[Prime Exclusive Discount End Date]
)
VAR MINIMUMDATE =
MINX ( tempTable, 'Deals and Coupons'[Coupon Start Date] )
VAR MAXIMUMDATE =
MAXX ( tempTable, 'Deals and Coupons'[Coupon End Date] )
VAR MINIMUMDATE2 =
MINX ( tempTable, 'Deals and Coupons'[Deal Start Date] )
VAR MAXIMUMDATE2 =
MAXX ( tempTable, 'Deals and Coupons'[Deal End Date] )
VAR MINIMUMDATE3 =
MINX ( tempTable, 'Deals and Coupons'[Prime Exclusive Discount Start Date] )
VAR MAXIMUMDATE3 =
MAXX ( tempTable, 'Deals and Coupons'[Prime Exclusive Discount End Date] )
VAR DATE2 =
MAXX ( tempTable, 'Deals and Coupons'[Lightning Deal Date] )
RETURN
IF (
AND (
SELECTEDVALUE ( 'Calendar'[Date] ) >= MINIMUMDATE,
SELECTEDVALUE ( 'Calendar'[Date] ) <= MAXIMUMDATE
),
"#A9F099",
IF (
AND (
SELECTEDVALUE ( 'Calendar'[Date] ) >= MINIMUMDATE2,
SELECTEDVALUE ( 'Calendar'[Date] ) <= MAXIMUMDATE2
),
"#97E6F8",
IF (
AND (
SELECTEDVALUE ( 'Calendar'[Date] ) >= MINIMUMDATE3,
SELECTEDVALUE ( 'Calendar'[Date] ) <= MAXIMUMDATE3
),
"#E6A1CB",
IF (
SELECTEDVALUE ( 'Calendar'[Date] ) = DATE2,
"#D1CEFF"
))))
4 Replies
Hi Anonymous ,
Did you mean that you want to put it to a slicer visual, then filter the related data by the slicer?
I cannot understand it, can you provide some sample data or the sample picture you want to realize?
Best Regards,
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
Is this column in 'Deals and Cupon 'table, or it has a relationship with 'Deals and Cupon 'table, the rows will be effected based on the relationship between them.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.