Forum Discussion
Helper IICalculated Column based on multiple Min & MAX conditions
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.
Helper IIHello 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.
Community ChampionNaderSaeed 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)- NaderSaeed
Hello Greg_Deckler
I really appreciate your time trying to help me out.
I tried your last formula, and unfortuantly it didn't give the expected results as well.
I'm sure it's due to my bad explanation.Let me attempt this one last time:
- If [Duration] in 'Table 1' falls between two values of the [Duration] in 'Table 2'. (for each Name and Level)
- to return 1, The [Index] in 'Table 1' associated with that Duration must be >= the [Index] in 'Table 2' assocaited with Minimum [Index] of the two values that the [Duration] is falling in between of.
Example:
- If Table 1 Duration is 2.7, then the Index has to be >= 60 to return 1, else it should be zero.
- Also if duration is < 2 it should return 0, because this duration less than the Min for that level in 'Table 2''Table 2'
Level Duration Index 3 2 60 3 3 40
I know I confused you enough here, I'm sorry 🙂- Greg_Deckler
NaderSaeed Post your sample data for Table1 and Table2 as text and I'll construct a working example.