SUMX and variable table not totalling correctly

[dujhe]

Hi,

I apologise if this has been asked previously but I have searched and cannot seem to find anything that I can relate to. I'm having an issue using SUMX on a table variable in a DAX measure. It seems to work on a row by row basis but the totals are appearing incorrectly.

Here is a sample of the data I am using. The EventStartRank is a calculated column created using a RANKX to rank the start date of EventTypes for each ServiceUserID.

ServiceUserID	EventType	EventStartDate	EventEndDate	Duration	EventStartRank
82728	Contact	22/11/2017	22/11/2017	0	1
82728	Contact	06/01/2018	06/01/2018	0	6
82728	Contact	09/01/2018	09/01/2018	0	7
82728	Date of death	09/01/2018	09/01/2018	0	7
82728	Reablement Plan	09/01/2018	09/01/2018	0	7
82728	Reablement service	09/01/2018	09/01/2018	0	7
82728	Contact	30/01/2018	30/01/2018	0	8
82728	Contact	30/01/2018	30/01/2018	0	8
100000032	Contact	22/11/2017	22/11/2017	0	1
100000032	Hospital episode	24/11/2017	05/04/2022	1593	2
100000032	Assessment	24/11/2017	27/11/2017	3	2
100000032	Case Holding Allocation	24/11/2017	01/12/2017	7	2
100000032	Contact	04/12/2017	04/12/2017	0	3
100000032	Contact	04/12/2017	05/12/2017	1	3
100000032	Case Holding Allocation	04/12/2017	05/12/2017	1	3
100000032	Case Holding Allocation	04/12/2017	05/12/2017	1	3
100000032	Contact	05/12/2017	05/12/2017	0	4
Z_EXAMPLE	Contact	01/04/2020	02/04/2020	1	1
Z_EXAMPLE	Contact	08/04/2020	08/04/2020	0	2
Z_EXAMPLE	Reablement Plan	10/04/2020	10/05/2020	30	3
Z_EXAMPLE	Review	09/05/2020	09/05/2020	0	4
Z_EXAMPLE	Short term service	11/05/2020	16/06/2020	36	5
Z_EXAMPLE	Swifts call out	17/07/2020	17/07/2020	0	6
Z_EXAMPLE	Swifts call out	28/07/2020	28/07/2020	0	7
Z_EXAMPLE	Swifts call out	30/08/2020	30/08/2020	0	8

I have 2 slicers on my report that are used to select EventTypes (which are 2 summarized tables on EventType). What I am trying to achieve is to select an end EventType and then select a prior to EventType and produce a distinct count of ServiceUserIDs that had a prior EventType start before or at the same time as the end EventType. 

This is the measure I have written and it just isn't quite right

Measure =

var _1a = SELECTEDVALUE(map_events[EventType])

var _2a = SELECTEDVALUE(map_events2[EventType])

var x = CALCULATE(
                          MIN('test table'[EventStartRank]),
                          'test table'[Eventtype] = _1a)

var z = CALCULATE(
                          MIN('test table'[EventStartRank]),
                          'test table'[EventType] = _2a)

var test = IF( x <= z, 1, 0)

var sumtab = ADDCOLUMNS(

                             SUMMARIZE(

                                      'test table',

                                      'test table'[LAS_ServiceUserID]),

                                       "Flag", test)

Return

SUMX(sumtab, [Flag])

When I place this measure into a table with ServiceUserID on the rows it works as I would expect (it flags 1 and 0 for those ServiceUserIDs as expected) however the total appears to just be counting the rows. This is the output I am receiving

I have tried creating a calculated table and filter that on the [FLAG] = 1 then use SUMX on that but that doesn't seem to work. I have tried to include a FILTER within the SUMX to filter [FLAG] = 1 and still again that does not appear to work. I'm not sure what the issue is, if it is something to do with the fact I am trying to work on a variable table or if my DAX is just incorrect.

I really have run out of ideas so any suggestions, advice or help would be greatly appreciated

[dujhe]

AH that makes sense now!

Thank you so much as this was driving me mad.

[PaulOlding · Accepted solution]

Hi @dujhe 

Your measure creates a sumtab table, with an added flag column that you use to do the SUMX.  However, the test variable that you're using to populate flag isn't being recalculated for each row of sumtab.  The value is fixed before sumtab is created and will therefore be the same for each row of sumtab.

What you can do is calculate x, z, and test within the ADDCOLUMNS, so these variables get evaluated for each row of sumtab separately.

Measure 2 = 
var _1a = SELECTEDVALUE(map_events[EventType])
var _2a = SELECTEDVALUE(map_events2[EventType])

var sumtab = 
ADDCOLUMNS(
    SUMMARIZE('test table','test table'[ServiceUserID]),
    "Flag", 
    var x = CALCULATE(
                          MIN('test table'[EventStartRank]),
                          'test table'[Eventtype] = _1a)
    var z = CALCULATE(
                          MIN('test table'[EventStartRank]),
                          'test table'[EventType] = _2a)
    var test = IF( x <= z, 1, 0)
    RETURN test
)
Return
    SUMX(sumtab, [Flag])