Forum Discussion
Column or measure Conditionals
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!