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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hell-1931
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:

EpisodesSample.PNG

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 -

EpisodesResultSample.PNG

 

Please - HELP!!!

 

2 REPLIES 2
lbendlin
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.

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.