Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.


Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.


Fabric certifications survey

Certification feedback opportunity for the community.