Sort By Column not working with Dax Measure in Matrix

[KevinCampisi]

Hello everyone,

I have a problem I could use some help with...

I have a report that has a unique requirement where I have to calculate a table of ProgramPlanIDs that have completed a specific Status for the selected date range and Schedule Type

That list of ProgramPlanIDs is then used to view the [Average Days To Complete] for other Statuses that those ProgramPlanIDs have completed

The DAX below accomplishes that UNTIL I change the Sort Order of the Status field to StatusSequenceNo and then all of the previous Statuses disappear.

Does anyone have any suggestions on how this might be fixed?

Any help would be greatly appreciated.

_Average Days to Complete - Calendar =
Var _SelectedDates = VALUES('Calendar Date'[CalendarDateKey])
Var _SelectedTeam = VALUES('Schedule Status'[TeamName])
Var _SelectedStatus = VALUES('Schedule Status'[StatusName])
Var _SelectedType = VALUES('Schedule Type'[ScheduleType])

VAR _SelectedPlans = CALCULATETABLE(VALUES('Plan Schedule'[ProgramPlanID])
, TREATAS(_SelectedDates, 'Plan Schedule'[ActualCompletionDateKey])
, TREATAS(_SelectedTeam, 'Plan Schedule'[StatusTeam])
, TREATAS(_SelectedStatus, 'Plan Schedule'[Status])
, TREATAS(_SelectedType , 'Plan Schedule'[ScheduleType])
)
RETURN
CALCULATE(
AVERAGEX(_SelectedPlans, [_Total Days to Complete])
)

_Total Days to Complete =
Var _SelectedType = VALUES('Schedule Type'[ScheduleType])
RETURN
CALCULATE(
sum('Plan Schedule'[DaysToComplete])
,'Plan Schedule'[ScheduleType] IN _SelectedType
,'Plan Schedule'[IsExcluded] = 0
)

The Power BI file is here:

https://1drv.ms/u/c/2c590f4c18b024c0/EQSoS2KtwlRGm0McnR7tDAMBWt5v9fKCHBQrDCSF2D17Gg?e=oBxc7f

Thanks,

Kevin

BEFORE SORT COLUMN IS APPLIED

AFTER SORT COLUMN IS APPLIED

[Anonymous]

Hi, @KevinCampisi 

After turning Status into StatusSequenceNo, what kind of effect do you ultimately want to achieve, and you can elaborate on the logic to achieve it.

Best Regards,
Yang
Community Support Team

[KevinCampisi]

Hello @Anonymous,

Ultimately, I would like the Column Headers in the image above to be the Status field and appear in StatusSequenceNo order. 

Concept Rack, Concept Elevation, Concept Redlines, Concept Redlines Review

To do this, I was trying to leverage the Sort By Column function in Column Tools.