Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grbs
Frequent Visitor

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 4
v-xinruzhu-msft
Community Support
Community Support

Hi @grbs ,

 

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 @v-xinruzhu-msft - yes that is what I meant. I have included an example slicer of how I would like it to appear. The first code sample I provided above, called "Coupon or Deal Column," acheives this visual, but it does not operate as a slicer to filter rows. Does that make sense?

grbs_0-1671030890260.png

 

Hi @grbs 

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.

 

I believe that this should take place in table 'X' that has a relationship with 'Deals and Coupon' table due to the fact that it must filter table 'X' data rows. I appreciate your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.