Forum Discussion
Helper IICalculated Column based on multiple Min & MAX conditions
Hello,
Need help to write a conditoonal column for 'Table 1'.
To check each Name's Level in 'Table 2' and applies the following logic:
If the Duration in 'Table 1' falls between 2 values for that Level's Duration in 'Table 2', 'Table 1'[Index] has to ALSO be >= 'Table 2'[Index] associated with the smaller value of the that 'Table 2'[Duraion].
For example:
For Joe below, His Index in 'Table 1' is "38", I want the calculated column to return "1" If, and only if, the Duration in 'Table 1' is >= 4.75.
In the case below, Joe's duration is 4.7, which is < 4.75 (i.e. the Duration in Table 2 associated with Index 38)
'Table 1'
| Name | Level | Duration | Index |
| Adam | 3 | 4.15 | 44 |
| Joe | 4 | 4.7 | 38 |
| Sam | 1 | 1.8 | 15 |
| Gerard | 2 | 4 | 23 |
| Jamie | 4 | 4.25 | 56 |
| Bane | 1 | 3 | 12 |
| Mike | 3 | 5 | 30 |
'Table 2'
| Level | Duration | Index |
| 1 | 1.5 | 22 |
| 1 | 2 | 10 |
| 2 | 3 | 39 |
| 2 | 3.5 | 33 |
| 2 | 4 | 27 |
| 2 | 4.5 | 15 |
| 3 | 4 | 56 |
| 3 | 4.25 | 50 |
| 3 | 4.5 | 44 |
| 3 | 4.75 | 38 |
| 3 | 5 | 32 |
| 3 | 5.25 | 26 |
| 3 | 5.5 | 20 |
| 4 | 4 | 56 |
| 4 | 4.25 | 44 |
| 4 | 4.5 | 42 |
| 4 | 4.75 | 38 |
| 4 | 5 | 32 |
| 4 | 5.25 | 26 |
| 4 | 5.5 | 20 |
Expected Results:
| Name | Level | Duration | Index | Expected |
| Adam | 3 | 4.15 | 44 | 0 |
| Joe | 4 | 4.7 | 38 | 0 |
| Sam | 1 | 1.8 | 15 | 0 |
| Gerard | 2 | 4 | 23 | 0 |
| Jamie | 4 | 4.25 | 56 | 1 |
| Bane | 1 | 3 | 10 | 1 |
| Mike | 3 | 5 | 30 | 0 |
Thank You
Hi NaderSaeed ,
According to your description, here’s my solution.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- Greg_Deckler
Community Champion
NaderSaeed Perhaps:
Expected = VAR __Duration = 'Table 1'[Duration] VAR __Index = 'Table 1'[Index] VAR __Level = 'Table 1'[Level] VAR __Table2Duration = MAXX(FILTER('Table 2',[Index]=__Index && [Level]=__Level),[Duration]) RETURN IF(__Duration >= __Table2Duration,1,0)
NaderSaeedHello Greg_Deckler
Thank you for attempting this.
I found that all the "0"s are 100% correct with my data set.
However, I found that some of the "1"s are not accurate.
For example, When I applied your calculated column on the in the example in the post.
I get the below:Name Level Duration Index Expected Adam 3 4.15 44 0 Joe 4 4.7 38 0 Sam 1 1.8 15 0 Gerard 2 4 23 0 Jamie 4 4.25 56 0 Bane 1 3 12 1 Mike 3 5 30 0 Jamie's expected column is giving 0 and it shoud be 1.
Because Jamie's Duration is 4.25, so according to Table 2, he's got to have an Index of 44 or higher to return "1" and he's got 56, so it should be 1.
'Table 2'
I'm also getting some "1"s where the Duration in 'Table 1' is smaller than the smallest value for Duration in 'Table 2' per level.
- Greg_Deckler
NaderSaeed Oh, I was misunderstanding and had Duration and Index mixed around, try:
Expected = VAR __Duration = 'Table 1'[Duration] VAR __Index = 'Table 1'[Index] VAR __Level = 'Table 1'[Level] VAR __Table2Index = MAXX(FILTER('Table 2',[Duration]=__Duration && [Level]=__Level),[Index]) RETURN IF(__Index >= __Table2Index,1,0)
v-yanjiang-msftCommunity Support
Hi NaderSaeed ,
According to your description, here’s my solution.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- NaderSaeed
