Calculated Column based on multiple Min & MAX conditions

[NaderSaeed]

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

[v-yanjiang-msft · Accepted solution]

Hi @NaderSaeed ,

According to your description, here’s my solution.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NaderSaeed]

@v-yanjiang-msft 

Genius ! Thank you so much! ❤️

[Greg_Deckler]

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

[NaderSaeed]

Hello @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)

[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.