Forum Discussion
Calculated column based on multiple conditions using DAX
Hello.
I could use some assistance regarding a DAX algorithm, since I'm inexperienced in algorithms of this complexity.
I have about 15k rows with data similar to the table shown below.
I wish to construct the Decision column shown below based on each week and each person and the following conditions:
- Decision should be =1 the first two days for each person and for each week (assuming the person has different values for Day_in_week_for_event (see rows with Ken).
- If the same person has an event the same day, Decision should prioritize classification A (see rows with Bob)
- If a person has 1 or more classification A in the same week, the first two A occurences in the week must be Decision =1 (see rows with Jenny).
- Decision is 0 if it isn't 1
| Person | Weeknumber | Day_in_week_for_event | Classification | Decision |
| Bob | 1 | 1 | B | 0 |
| Bob | 1 | 1 | A | 1 |
| Jenny | 2 | 1 | B | 1 |
| Jenny | 2 | 2 | B | 1 |
| Jenny | 2 | 3 | A | 1 |
| Jenny | 2 | 4 | A | 1 |
| Jenny | 2 | 5 | A | 0 |
| Ken | 2 | 2 | A | 1 |
| Ken | 2 | 4 | B | 1 |
| Ken | 2 | 5 | B | 0 |
| Erick | 3 | 2 | B | 1 |
Any help will be much appreciated.
Let me know if you have any questions.
Hi, CBXS
First, you need to add an index column to Power Query.
Column = Var _N1=CALCULATE(COUNT('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Classification],'Table'[Weeknumber],'Table'[Day_in_week])) Var _N2=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Classification],'Table'[Weeknumber],'Table'[Day_in_week])) Var _N3=CALCULATE(COUNT('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Weeknumber],'Table'[Day_in_week])) Var _Rank=RANKX(FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])),[Day_in_week],,ASC) Return IF(_N1>1&&[Index]=_N2,1,IF(_N3>1&&[Classification]="A",1,IF(_Rank<=2&&_N3=1,1,BLANK())))One week more A = Var _RankA=IF([Classification]="A",RANKX(FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])&&[Classification]="A"),[Day_in_week],,ASC),BLANK()) Return IF(_RankA<=2&&_RankA<>BLANK()&&[Column]=BLANK(),1,BLANK())Decision = SWITCH(TRUE(), [Column]=1,1, [One week more A]=1,1, 0)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi, CBXS
You can try the following methods.
One week first two days = Var _Rank=RANKX(FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])),[Day_in_week_for_event],,ASC) Return IF(_Rank<=2,1,0)One day more events = Var _Countevent=CALCULATE(COUNT('Table'[Day_in_week_for_event]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Day_in_week_for_event]=EARLIER([Day_in_week_for_event])&&[Weeknumber]=EARLIER('Table'[Weeknumber]))) Return IF(_Countevent>1&&[Classification]="A",1,IF(_Countevent=1,[One week first two days],0))Decision = Var _CountA=CALCULATE(COUNT('Table'[Classification]),FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])&&[Classification]="A")) Var _RankA=IF([Classification]<>"A",BLANK(),RANKX(FILTER('Table',[Classification]="A"&&[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])),[Day_in_week_for_event],,ASC)) return IF(_CountA>=1&&_RankA<=2&&_RankA<>BLANK(),1,[One day more events])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Almost perfect!
It doesn't look like the "One day two events" takes the weeknumber into account, as the Person= Erick row should be a 0 in the "One day two events" column, since it is the only row with weeknumber=3.
Additionally, i forgot to mention another condition. If there are multiple events for a person on the same day in the same week, the Decision should be 1 in only one of those rows. Your code does this when the two events have different classification, but not if the classification is the same. It doesn't matter which row that gets Decision = 1.
See the desired result in the last two rows with Person = Erick
Person Weeknumber Day_in_week Classification Decision Bob 1 1 B 0 Bob 1 1 A 1 Jenny 2 1 B 1 Jenny 2 2 B 1 Jenny 2 3 A 1 Jenny 2 4 A 1 Jenny 2 5 A 0 Ken 2 2 A 1 Ken 2 4 B 1 Ken 2 5 B 0 Erick 3 2 B 1 Erick 3 2 B 0 Thank you so much for helping me with my problem!
Hi, CBXS
First, you need to add an index column to Power Query.
Column = Var _N1=CALCULATE(COUNT('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Classification],'Table'[Weeknumber],'Table'[Day_in_week])) Var _N2=CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Classification],'Table'[Weeknumber],'Table'[Day_in_week])) Var _N3=CALCULATE(COUNT('Table'[Index]),ALLEXCEPT('Table','Table'[Person],'Table'[Weeknumber],'Table'[Day_in_week])) Var _Rank=RANKX(FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])),[Day_in_week],,ASC) Return IF(_N1>1&&[Index]=_N2,1,IF(_N3>1&&[Classification]="A",1,IF(_Rank<=2&&_N3=1,1,BLANK())))One week more A = Var _RankA=IF([Classification]="A",RANKX(FILTER('Table',[Person]=EARLIER('Table'[Person])&&[Weeknumber]=EARLIER('Table'[Weeknumber])&&[Classification]="A"),[Day_in_week],,ASC),BLANK()) Return IF(_RankA<=2&&_RankA<>BLANK()&&[Column]=BLANK(),1,BLANK())Decision = SWITCH(TRUE(), [Column]=1,1, [One week more A]=1,1, 0)Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
