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
Anonymous
Not applicable

Filter orders with different products/ options

Hi all,

I have a similar table: 

 

 

YR6L4AJ

 

 

 

 

 

 

 

 

 

 

 

 

One row per order/ product/ option. I am trying to create a column which will filter orders that have:

1) different products

2) same products but different options

3) same product with same option

4) a mix of everything

 

There are only 3 products, but many options which can work for same product (i.e. xx can be for A,B or C products).

 

I tried to FILTER(EARLIER) but could not figure out how to invorporate all products or all options.

 

Hopefully I made myself clear 🙂 

 

Any help will be much appreciated !

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this column

 

Column =
VAR OrderProduct =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Product]
        )
    )
VAR OrderProductOption =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Product],
            Table1[Option]
        )
    )
VAR OrderOption =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Option]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        OrderProductOption = 1, "Same",
        AND ( OrderProduct = 1, OrderProductOption > 1 ), "Mix Option",
        AND ( OrderOption = 1, OrderProduct > 1 ), "Mix Product",
        "Mix All"
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this column

 

Column =
VAR OrderProduct =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Product]
        )
    )
VAR OrderProductOption =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Product],
            Table1[Option]
        )
    )
VAR OrderOption =
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Table1, Table1[Order] = EARLIER ( Table1[Order] ) ),
            Table1[Option]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        OrderProductOption = 1, "Same",
        AND ( OrderProduct = 1, OrderProductOption > 1 ), "Mix Option",
        AND ( OrderOption = 1, OrderProduct > 1 ), "Mix Product",
        "Mix All"
    )

Regards
Zubair

Please try my custom visuals

@Anonymous

 

Please see attached file as well

 

Filterorders.png


Regards
Zubair

Please try my custom visuals

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.