Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CBXS
Frequent Visitor

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

 

PersonWeeknumberDay_in_week_for_eventClassificationDecision
Bob11B0
Bob11A1
Jenny21B1
Jenny22B1
Jenny23A1
Jenny24A1
Jenny25A0
Ken22A1
Ken24B1
Ken25B0
Erick32B1

 

Any help will be much appreciated.

Let me know if you have any questions.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @CBXS 

 

First, you need to add an index column to Power Query.

vzhangti_1-1676516063041.png

 

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)

 

vzhangti_2-1676516117357.png

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.

 

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
Community Support

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

vzhangti_0-1676344500846.png

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

PersonWeeknumberDay_in_weekClassificationDecision
Bob11B0
Bob11A1
Jenny21B1
Jenny22B1
Jenny23A1
Jenny24A1
Jenny25A0
Ken22A1
Ken24B1
Ken25B0
Erick32B1
Erick32B0

 

Thank you so much for helping me with my problem!

v-zhangti
Community Support
Community Support

Hi, @CBXS 

 

First, you need to add an index column to Power Query.

vzhangti_1-1676516063041.png

 

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)

 

vzhangti_2-1676516117357.png

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:

CBXS_1-1676644102345.png

 

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

PersonWeeknumberDay_in_weekClassificationDecision
Bob11B0
Bob11A1
Jenny21B1
Jenny22B1
Jenny23A1
Jenny24A1
Jenny25A0
Ken22A1
Ken24B1
Ken25B0
Erick32B1
Erick32B0
Jenny26A0
Jenny25B0
Chris61A1
Chris61A0
Chris65A1
Chris65A0
Chris66A0
Chris66A0
Chris67A0
Chris67A0
Derp71B1
Derp71B0
Derp75B1
Derp75B0
Derp76B0
Derp76B0
Derp77B0
Derp77B0

 

I attempted to edit your code to achieve the desired result, but I am not experienced enough in DAX, unfortunately.

 

CBXS
Frequent Visitor

@v-zhangti are you able to assist me with the request above?

 

It would be much appreciated !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.