topic Re: Column or measure Conditionals in DAX Commands and Tips

Column or measure Conditionals

blai — Wed, 08 Nov 2023 13:22:56 GMT

Hi, I have a column with this conditions:

ConditionalColumn=

VAR userID= [UserID]

VAR IDduplicated = CALCULATE(COUNTROWS(table), ALLEXCEPT(table, table[UserID])) > 1

VAR shopIDduplicated =

IF(

IDduplicated,

CALCULATE(

COUNTROWS(table),

ALLEXCEPT(table, table[UserID]),

table[shopID] = EARLIER(table[shopID])

) > 1,

FALSE()

)

RETURN

IF(

IDduplicated && shopIDduplicated,

"Yes",

"No"

)

I would rather, if UserID is duplicated, for each duplicated UserID, take their shopID. If the shopID is duplicated, put "Yes"; otherwise, put "No". Below, I show you what this conditional column is doing and what i want:

Example: I have 3 duplicated UserIDs, but 2 of them have the same shopID. I want the conditional column to show "Yes", only for those 2 lines; for the oder UserID, it becames "No".

What's wrong is in lines where UserID is "22" (lines number 2-3-4). It has "Yes" for all 3, but first shopID isn't equal to others, so it should be "No", "Yes", "Yes".

Conditional//shopID//categoryID//userID//YearStart;MonthStart;DayStart//YearFinish;MonthFinish;DayFinish
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!

Re: Column or measure Conditionals

ERD — Wed, 08 Nov 2023 13:59:12 GMT

Hi blai , why your start dates are later then the end dates?

Re: Column or measure Conditionals

ERD — Wed, 08 Nov 2023 14:05:49 GMT

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" )

Re: Column or measure Conditionals

blai — Wed, 08 Nov 2023 16:19:52 GMT

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!