Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

DAX Calculated Column is not working correctly

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 =
        '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 =
        'Client Episodes Monthly Rollup'[ClientWHID],
            MAXX (
                'Client Episodes Monthly Rollup',
                'Client Episodes Monthly Rollup'[LengthOfStayToDate]

    MAXX ( MaxLOSByClient, [MaxLOS] )
    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!!!


Super User
Super User

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.


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



Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.