Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
EXCEPTIONS (IMPORTANT!!)
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
Solved! Go to 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.
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.
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 🙂
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
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |