How to create groups and apply conditions to them?

[Anonymous]

Hi,

I must add two columns (or one column and one measure maybe) to my data to answer a business problem.

My original data is as below:

ServiceId	Service#	Date	StoreId	Customer #	TypeId	$	Purchase
3	12345678	43952	1	123	2	45	Standard 45
19	12345679	43952	1	123	2	30	Standard 30
6	12345680	43952	1	124	2	36	Standard 36
15	12345681	43952	1	125	2	55	Standard 55
7	12345682	43952	1	126	2	60	Standard 60
23	12345683	43952	1	128	2	45	Standard 45
22	12345684	43952	1	129	5	30	Standard 30
13	12345685	43952	2	123	1	45	Standard 45
14	12345686	43952	2	124	2	45	Standard 45
8	12345687	43952	2	125	2	30	Standard 30
21	12345688	43952	2	125	5	45	Standard 45
24	12345689	43952	2	126	2	45	Standard 45
11	12345690	43952	2	127	3	30	Standard 30
10	12345691	43952	2	128	3	45	Standard 45
18	12345692	43952	2	129	2	27	Standard 27
25	12345693	43952	2	130	2	45	Standard 45
26	12345694	43952	2	131	1	30	Standard 30
5	12345695	43953	1	123	3	36	Standard 36
9	12345696	43953	1	123	3	1	Double
20	12345697	43953	1	124	2	60	Standard 60
17	12345698	43953	1	127	2	45	Standard 45
1	12345699	43953	1	132	3	30	Standard 30
2	12345700	43953	1	132	3	1	Double
4	12345701	43953	1	132	3	45	Standard 45
29	12345702	43953	2	139	2	30	Standard 30
16	12345703	43953	2	125	1	120	Standard 120
12	12345704	43953	2	125	1	0	Standard 0
16	12345703	43953	2	125	1	0	Standard 0
12	12345704	43953	2	125	1	0	Standard 0

Firstly, I need to create a "TransactionID" that groups services by same date, same store & same customer

Secondly, I need a way to determine whether the services within each group are valid or not. The conditions for determining this are explained below. I am thinking to create a column that is called “IsValid?” and returning true or false – or can I do this via a measure ??

Conditions:

For a service to trigger the Is Valid flag, within each of the transaction groups:

• Must be more than one service within group
• Service # must be unique within group
• $ column does not equal 0
• At least one row must have Type Id =
  • 1 OR 5 OR 10 OR 11 OR 12 OR 3

EXCEPTIONS (IMPORTANT!!)

• 2 Services where TypeID = 3 and one of the values in column purchase contains “double” and the other does not is classified as a BUNDLE
  • Bundles alone should not trigger the valid flag
  • Bundles only are valid when coupled with another service (of any TypeID) as the non-“double” component should trigger the IsValid flag when accompanied with any other service
  • THEREFORE: Rows containing Type = 3 but column purchase contains “double” must not contribute to triggering the valid flag

My expected results as well as an explanation are given below:

Colours have been used to identify a transaction group

I kindly appreciate any help or guidance in the right direction that will help me achieve my two goals.

Thanks

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

I created data to reproduce your scenario with the conditions. You may try the following calculated columns and measure.

Calculated column:
Transaction ID = RANKX('Table','Table'[New Index],,ASC,Dense)
New Index = 
var a = CALCULATE(COUNT('Table'[Date]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[StoreId]=EARLIER('Table'[StoreId])&&'Table'[Customer#]=EARLIER('Table'[Customer#])))
return
IF(a=1,'Table'[Index],CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[StoreId]=EARLIER('Table'[StoreId])&&'Table'[Customer#]=EARLIER('Table'[Customer#]))))

Measure:
IsValid = 
VAR a =
    CALCULATE (
        COUNT ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] IN FILTERS ( 'Table'[Date] )
                && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
        )
    )
VAR b =
    CALCULATE (
        MAX ( 'Table'[TypeID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] IN FILTERS ( 'Table'[Date] )
                && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
        )
    )
RETURN
    SWITCH (
        TRUE,
        a = 1, "single service no group",
        a = 2
            && b IN { 1, 3, 5, 10, 11, 12 }
            && MAX ( 'Table'[TypeID] ) = 3
            && CONTAINS (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] IN FILTERS ( 'Table'[Date] )
                        && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                        && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
                ),
                'Table'[Purchase], "Double"
            ), "contains two services with originID=3 but one consists of 'double'-BUNDLE",
        a >= 2
            && NOT ( b IN { 1, 3, 5, 10, 11, 12 } ), "doesn't contain a valid typeID",
        a > 2
            && b IN { 1, 3, 5, 10, 11, 12 }
            && MAX ( 'Table'[TypeID] ) = 3
            && CONTAINS (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] IN FILTERS ( 'Table'[Date] )
                        && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                        && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
                ),
                'Table'[Purchase], "Double"
            ), "contains two services with originID=3 where purchase not 'double'",
        a >= 2
            && CONTAINS (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] IN FILTERS ( 'Table'[Date] )
                        && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                        && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
                ),
                'Table'[$], 0
            ), "not valid since accompanying services have $=0",
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[Service#] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Date] IN FILTERS ( 'Table'[Date] )
                    && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                    && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
            )
        ) = a
            && b IN { 1, 3, 5, 10, 11, 12 }
            && NOT (
                CONTAINS (
                    FILTER (
                        ALLSELECTED ( 'Table' ),
                        'Table'[Date] IN FILTERS ( 'Table'[Date] )
                            && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                            && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
                    ),
                    'Table'[Purchase], "Double"
                )
            ), "group contains a valid type("
            & CALCULATE (
                MAX ( 'Table'[TypeID] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Date] IN FILTERS ( 'Table'[Date] )
                        && 'Table'[StoreId] IN FILTERS ( 'Table'[StoreId] )
                        && 'Table'[Customer#] IN FILTERS ( 'Table'[Customer#] )
                )
            ) & ")"
    )

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan,

This almost worked.

For the IsValid column, all I require is two values either 0 or 1 being false or true. I don't require those explanations that you have put in the formula, they are purely for you to understand the categories.

Also, I think it would be best if the 'IsValid' column was a calculated column as I'd like to be able to filter for services where IsValid = 1 or = 0. Is that possible with a measure or is a calculated column better?

Pls let me know how to change it so I only see 0's and 1's.

Thanks 🙂 

[v-alq-msft]

Hi, @Anonymous 

You may modify the text to the corresponding status(0 or 1). We suggest you use measure, which has better performance than a calculated column. The value of a calculated column is computed during data refresh and uses the current row as a context, it does not depend on user activity on the pivot table. A measure operates on aggregations of data defined by the current context.  

Best Regards

Allan

[amitchandak]

@Anonymous , You have to create a new column like this

new column = 
var _1= sumx(filter(table, table[customer] = earlier(table[customer]) && table[date] = earlier(table[date]) 
		&& table[store] = earlier(table[store])),[$] )
var _2= calculation( distinctcount([service #]) ,filter(table, table[customer] = earlier(table[customer]) && table[date] = earlier(table[date]) 
		&& table[store] = earlier(table[store]))
return 
if (_1 >0 && _2>1, "Single Service No group", "Exception")

You have to add new conditions as per need

In case you want a measure you have to use allexpect

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Anonymous]

Hi, 
I was unable to execute or understand how this column would solve the problem. Also, I do not require a column for the explanation.

The only two columns I need to generate are Transaction ID and Valid Group as depicted in my excel file.

See photo for error