Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a similar table:
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 !
Solved! Go to Solution.
@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" )
@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" )
@Anonymous
Please see attached file as well
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |