Forum Discussion

srlabhe's avatar
srlabhe
Super User
1 year ago
Solved

Finding Max Days per row

Hi PBI Gurus

I have below data 

 

Where Days CO is a measure calculated based on selected date in slicer like below

=
var _maxDate=[MaxAsOfDateSelected]
var _diff=
NETWORKDAYS(SELECTEDVALUE(Table[Date]),_maxDate)
return
_diff
Now I wanted to have MO and Days MO Calculated as 
MO= CO who has max no of days 
Days MO= No of Days of MO
SO ideally it shoudl look like below 

I tried to use AllExcept , Selected Value , MAxx but its not working, I see its simple but something is missing.

Help appreciated 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi srlabhe 

    Try this:

    CO with top days = 
CALCULATE (
    -- Get the CO with the highest [Days CO] in the current filter context
    MAXX (
        TOPN (
            1,
            -- Create a table of COs with their corresponding [Days CO] values
            SUMMARIZECOLUMNS ( 'Table'[CO], "@value", [Days CO] ),
            [@value], DESC  -- Sort descending by [Days CO] and take top 1
        ),
        [CO]  -- Return the CO name (could also use [@value] to return the max value)
    ),
    ALLSELECTED ()  -- Respect slicers and visuals while removing row context
)

     

     

15 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    Hi srlabhe please try this calculated column

     

    Days MO =
    CALCULATE(
        MAX('TaskTable'[Days CO]),
        ALLEXCEPT('TaskTable', 'TaskTable'[Eid])
    )
     
     
    MO =
    VAR _maxDays =
        CALCULATE(
            MAX('TaskTable'[Days CO]),
            ALLEXCEPT('TaskTable', 'TaskTable'[Eid])
        )
    RETURN
    CALCULATE(
        MAX('TaskTable'[CO]),
        FILTER(
            'TaskTable',
            'TaskTable'[Days CO] = _maxDays &&
            'TaskTable'[Eid] = EARLIER('TaskTable'[Eid])
        )
    )
     
     
    • srlabhe's avatar
      srlabhe
      Super User
      1 year ago

      While calculating Days MO it gives me below error

      The MAX function only accepts a column reference as the argument number 1.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        1 year ago

        srlabhe Correct, since it is a measure, you have to do it via measure aggregation as I mentioned.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    srlabhe This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149 

    The pattern is:
    MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
    etc.

    • srlabhe's avatar
      srlabhe
      Super User
      1 year ago

      Hmm,

      is there any way we check if MO=CO then take Days CO and print it on all rows of the Eid?

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi srlabhe 

    Try this:

    CO with top days = 
CALCULATE (
    -- Get the CO with the highest [Days CO] in the current filter context
    MAXX (
        TOPN (
            1,
            -- Create a table of COs with their corresponding [Days CO] values
            SUMMARIZECOLUMNS ( 'Table'[CO], "@value", [Days CO] ),
            [@value], DESC  -- Sort descending by [Days CO] and take top 1
        ),
        [CO]  -- Return the CO name (could also use [@value] to return the max value)
    ),
    ALLSELECTED ()  -- Respect slicers and visuals while removing row context
)

     

     

    • srlabhe's avatar
      srlabhe
      Super User
      1 year ago

      Sorry but when I go to summary level it shows me sum of all and not Eid level data ....

    • srlabhe's avatar
      srlabhe
      Super User
      1 year ago

      Days with MO shows as below if not filtered for ant Eid

       

  • burakkaragoz's avatar
    burakkaragoz
    Super User
    1 year ago

    Hi srlabhe ,

    Looking at this thread, I notice there's some overcomplicated DAX being suggested when simpler solutions would work better.

    For your specific requirement:

    MO = 
VAR MaxDaysInGroup = 
    CALCULATE(
        MAXX(
            ALLEXCEPT(Table, Table[Eid]),
            [Days CO]
        )
    )
RETURN
    CALCULATE(
        SELECTEDVALUE(Table[CO]),
        FILTER(
            ALLEXCEPT(Table, Table[Eid]),
            [Days CO] = MaxDaysInGroup
        )
    )

Days MO = 
    CALCULATE(
        MAXX(
            ALLEXCEPT(Table, Table[Eid]),
            [Days CO]
        )
    )

    Why this approach works well:

    • Uses the reliable ALLEXCEPT pattern that handles measure context properly
    • Avoids complex table functions that can behave inconsistently across different DAX engine versions
    • More maintainable and easier to debug

    Alternative using the measure aggregation pattern mentioned earlier:

    Days MO = MAXX(
    SUMMARIZE(
        ALLEXCEPT(Table, Table[Eid]),
        Table[CO],
        "@DaysCO", [Days CO]
    ),
    [@DaysCO]
)

    The key insight here is that when working with measures in calculated columns or other measures, you want to use patterns that have proven stability rather than newer functions that might have edge cases or version dependencies.

    Both approaches handle your Eid-level grouping correctly while finding the CO with maximum days.

    If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
    Note: As Einstein said, "Everything should be made as simple as possible, but not simpler" - this applies perfectly to DAX solutions.
    This response was assisted by AI for translation and formatting purposes.

    • srlabhe's avatar
      srlabhe
      Super User
      1 year ago

      MO is already available in data , we nee dto calculate Days MO and the formula you provided not working 

  • v-dineshya's avatar
    v-dineshya
    Community Support
    1 year ago

    Hi srlabhe ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    I have created MO and Days MO Measure.

     

    Please refer below output snap and attached PBIX file.

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh