Forum Discussion
Column or measure Conditionals
Hi, I have a column with this conditions:
Column
| No | 111 | 1 | 11 | 2023 | November | 10 | 2023 | October | 9 |
| Yes | 222 | 2 | 22 | 2023 | February | 28 | 2023 | January | 16 |
| Yes | 333 | 3 | 22 | 2023 | October | 6 | 2023 | October | 1 |
| Yes | 333 | 3 | 22 | 2023 | October | 16 | 2023 | October | 16 |
| No | 444 | 4 | 33 | 2023 | June | 1 | 2023 | May | 23 |
| Yes | 555 | 5 | 44 | 2023 | November | 3 | |||
| Yes | 555 | 5 | 44 | 2023 | January | 19 | 2023 | January | 10 |
| Yes | 555 | 6 | 44 | 2023 | July | 28 | 2023 | February | 13 |
| Yes | 777 | 5 | 44 | 2023 | November | 2 | 2023 | August | 14 |
| Yes | 666 | 9 | 55 | 2023 | May | 31 | 2023 | April | 17 |
| Yes | 666 | 9 | 55 | 2023 | July | 21 | 2023 | June | 1 |
PS: The next level would be: once you have those "Yes" values, filter them for the same UserID, shopID, and check if they have the same categoryID. If they do, this would be the real "Yes"; the others would be "No". In that case, taking the table example, for the UserId "44", the correct sequence would be: Yes-Yes-No-No.
PS2: The final level would involve, once you have level 2, comparing the start date with the end date for lines that meet the previous criteria. If datediff is <=5 days, it should be "Yes"; otherwise, "No". Next step would be to do the same for the end date, comparing it with other lines' start dates. Before this, thake the next line and do the same... However, it's more important to have levels 1 first and 2 correct, not this level.
Any help is appreciated. Thanks!
3 Replies
Sorry, I turned the headings!
Tanks for the response!
By the way 2 things:
1. If level3 were correctly done, in the example table, all values should be "No". This is because there aren't any startDate or endDate from other lines wher the datediff is <=5 days. The code you provided compares a line's own startDate with its own endDate, but what I want is to compare startDates and endDates from different lines, always complying with the previous characteristics or at least with the same UserID.
2. The idea is to unify all levels, but if that's not posible, I would create 3 filters with all conditional columns and check all 3 "Yes".
PS: small detail: the dates are date type, and startDate is taken from a related table. Additionally, shopID is a created column related from other table
PS2: I would be happy if i could filter same UserID and shopID, (level1). To copy that and change the code with categoryID to filter both columns.
To simplify things: Here are examples, but before I explain you in words.
Level1
Only "Yes" when the same shopID and UserID
ShopID UserID Conditional 11 1
Yes
11 1 Yes 22 1 No 33 1 No 22 2 No 22 3 No 33 4 Yes 33 4 Yes Level2:
Only "Yes" when the same shopID, UserID, and categoryID.
shopID CategoryID UserID Conditional 11 111 1 Yes 11 111 1 Yes 11 111 1 Yes 11 222 1 No 22 111 1 No 11 111 2 No 22 111 2 Yes 22 111 2 Yes 33 444 3 No 55 333 4 No Level3: date below dd-mm-yyyy
Compare the startDate and endDate for the same shopID, UserID, and categoryID with other lines. If the date difference is less than or equal to 5 days, set Conditional to "Yes."
shopID CategoryID UserID startDate EndDate Conditional 11 111 1 08/11/2023 09/11/2023 No 11 111 1 25/11/2023 25/12/2023 Yes 11 111 1 28/12/2023 11/01/2023 Yes 22 111 1 13/01/2023 14/01/2023 No 33 444 2 08/11/2023 10/11/2023 No 33 444 3 08/11/2023 10/11/2023 Yes 33 444 3 15/09/2023 03/11/2023 Yes 44 444 3 1/01/2023 20/01/2023 Yes 44 444 3 22/01/2023 22/01/2023 Yes Thanks for the help, and if you have any more questions, I'm here to assist!
blai , you can play with these calculated columns to achieve your goal. Since the end date is earlier then start date, I had to change their placement in DATEDIFF formula.
level0 = VAR userIDs = CALCULATE ( COUNT ( t[userID] ), ALLEXCEPT ( t, t[userID] ) ) RETURN IF ( userIDs > 1, "Yes", "No" )level1 = VAR shopIDs = CALCULATE ( COUNT ( t[shopID] ), ALLEXCEPT ( t, t[userID], t[shopID] ) ) RETURN IF ( shopIDs > 1, "Yes", "No" )level2 = VAR categoryIDs = CALCULATE ( COUNT ( t[categoryID] ), ALLEXCEPT ( t, t[userID], t[shopID], t[categoryID] ) ) RETURN IF ( categoryIDs > 1, "Yes", "No" )level3 = VAR categoryIDs = CALCULATE ( COUNT ( t[categoryID] ), ALLEXCEPT ( t, t[userID], t[shopID], t[categoryID] ) ) VAR dt_start = IF ( NOT ISBLANK ( t[YearStart] ), DATEVALUE ( t[YearStart] & "/" & t[MonthStart] & "/" & t[DayStart] ) ) VAR dt_end = IF ( NOT ISBLANK ( t[YearFinish] ), DATEVALUE ( t[YearFinish] & "/" & t[MonthFinish] & "/" & t[DayFinish] ) ) VAR diff = DATEDIFF ( dt_start, dt_end, DAY ) RETURN IF ( categoryIDs > 1 && diff <= 5, "Yes", "No" )
