DAX Calculated Column is not working correctly

Hell-1931 — Wed, 24 May 2023 03:02:29 GMT

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!!!

Re: DAX Calculated Column is not working correctly

lbendlin — Thu, 25 May 2023 01:41:57 GMT

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.

Re: DAX Calculated Column is not working correctly

Hell-1931 — Thu, 25 May 2023 10:28:31 GMT

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

topic DAX Calculated Column is not working correctly in Developer

DAX Calculated Column is not working correctly

Re: DAX Calculated Column is not working correctly

Re: DAX Calculated Column is not working correctly