DAX Calculated Column is not working correctly

[Hell-1931]

I have the following table with over 10K records in my SSAS Tabular model

Client Episodes Monthly Rollup:

I need to create a Calculated Column in DAX - [DaysGroups].

This Column should assign days intervals - "0-90" Days, "91-180" Days, "181-270" Days," 270+" Days, based on the highest [LengthOfStayToDate] value, for each [ClientWHID], where [DischargeDate] = 2050-12-31 and [EpisodeStatus] = "ACTIVE".

In the example above - there are 2 ClientWHIDs - 30636, 37623.

If [ClientWHID] = 30636,

[DischargeDate] = 2050-12-31 and 

[EpisodeStatus] = "ACTIVE", 

then - the highest [LengthOfStayToDate] value for this Client is 335.

This means, my Calculated Column [DaysGroups] should assign "270+" Days interval for all records where [ClientWHID] = 30636.

If [ClientWHID] = 37623,

[DischargeDate] = 2050-12-31 and

[EpisodeStatus] = "ACTIVE",

then the highest the highest [LengthOfStayToDate] value for this Client is 2 (only one value in this example).

This means, my Calculated Column [DaysGroups] should have all records "0-90" Days for [ClientWHID] = 37623.

I have the following DAX code, but it assigns my ALL ClientWHIDs with "270+" Days interval

VAR ActiveRecords =
  CALCULATETABLE (
        'Client Episodes Monthly Rollup',
          FILTER (
            'Client Episodes Monthly Rollup',
            'Client Episodes Monthly Rollup'[EpisodeStatus] = "ACTIVE"
        && 'Client Episodes Monthly Rollup'[DischargeDate] = DATE ( 2050, 12, 31 )
                
             )
            )

VAR MaxLOSByClient =
    SUMMARIZE (
        ActiveRecords,
        'Client Episodes Monthly Rollup'[ClientWHID],
        "MaxLOS",
            MAXX (
                'Client Episodes Monthly Rollup',
                'Client Episodes Monthly Rollup'[LengthOfStayToDate]
                 )
              )

VAR MaxLOS =
    MAXX ( MaxLOSByClient, [MaxLOS] )
RETURN
    SWITCH (
        TRUE (),
        MaxLOS >= 0
            && MaxLOS <= 90, "0-90",
        MaxLOS >= 91
            && MaxLOS <= 180, "91-180",
        MaxLOS >= 181
            && MaxLOS <= 270, "181-270",
        MaxLOS > 270, "270+",
        BLANK ()
           )

It results with ALL my ClientWHIDs = 270+ Days interval

See below, from my model -

Please - HELP!!!

[lbendlin]

I need to create a Calculated Column in DAX - [DaysGroups].

Are you sure you want to do that from a connection to a SSAS tabular source?  Is your data model  in mixed mode?

Usually this would have to be a measure, or implemented in the source.

[Hell-1931]

lbendlin

The answers for the 1st 2 questions are "Yes"

If this can be a measure - that's ok!  Anything that will work the way I described