Forum Discussion
Solved
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 ...
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.
It works as intended for the examples shown above.
Although there are still different combinations where the Decision column isn't the desired result:
A person should only have two Decision = 1 for a given week if the classification is the same through the week, see the desired result below (Chris and Derp):
| 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 |
| Jenny | 2 | 6 | A | 0 |
| Jenny | 2 | 5 | B | 0 |
| Chris | 6 | 1 | A | 1 |
| Chris | 6 | 1 | A | 0 |
| Chris | 6 | 5 | A | 1 |
| Chris | 6 | 5 | A | 0 |
| Chris | 6 | 6 | A | 0 |
| Chris | 6 | 6 | A | 0 |
| Chris | 6 | 7 | A | 0 |
| Chris | 6 | 7 | A | 0 |
| Derp | 7 | 1 | B | 1 |
| Derp | 7 | 1 | B | 0 |
| Derp | 7 | 5 | B | 1 |
| Derp | 7 | 5 | B | 0 |
| Derp | 7 | 6 | B | 0 |
| Derp | 7 | 6 | B | 0 |
| Derp | 7 | 7 | B | 0 |
| Derp | 7 | 7 | B | 0 |
I attempted to edit your code to achieve the desired result, but I am not experienced enough in DAX, unfortunately.