Finding Max Days per row

[srlabhe]

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 

[v-dineshya]

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

[burakkaragoz]

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]

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

[danextian · Accepted solution]

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]

Allselected()  -- Respect slicers and visuals while removing row context

Seems this is the issue , I want the Max of Days CO to refletc for each Eid even if the visual is not filtered

[srlabhe]

Its not working as expected

[srlabhe]

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

[srlabhe]

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

[Greg_Deckler]

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

Hmm,

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

[techies]

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]

While calculating Days MO it gives me below error

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

[Greg_Deckler]

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

[srlabhe]

So it should be in my case 

Days MO=maxx(summarize(Table,Eid,"Measure", [Days CO]),[Days CO])

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.