Forum Discussion

NaderSaeed's avatar
NaderSaeed
Icon for Helper II rankHelper II
4 years ago
Solved

Calculated 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...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    NaderSaeed
    Icon for Helper II rankHelper II
    4 years ago

    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:

     

    NameLevelDurationIndexExpected
    Adam34.15440
    Joe44.7380
    Sam11.8150
    Gerard24230
    Jamie44.25560
    Bane13121
    Mike35300

     

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        NaderSaeed
        Icon for Helper II rankHelper II
        4 years ago

        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'

        LevelDurationIndex
        3260
        3340



        I know I confused you enough here, I'm sorry 🙂