cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dujhe
Regular Visitor

SUMX and variable table not totalling correctly

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.

 

ServiceUserIDEventTypeEventStartDateEventEndDateDurationEventStartRank
82728Contact22/11/201722/11/201701
82728Contact06/01/201806/01/201806
82728Contact09/01/201809/01/201807
82728Date of death09/01/201809/01/201807
82728Reablement Plan09/01/201809/01/201807
82728Reablement service09/01/201809/01/201807
82728Contact30/01/201830/01/201808
82728Contact30/01/201830/01/201808
100000032Contact22/11/201722/11/201701
100000032Hospital episode24/11/201705/04/202215932
100000032Assessment24/11/201727/11/201732
100000032Case Holding Allocation24/11/201701/12/201772
100000032Contact04/12/201704/12/201703
100000032Contact04/12/201705/12/201713
100000032Case Holding Allocation04/12/201705/12/201713
100000032Case Holding Allocation04/12/201705/12/201713
100000032Contact05/12/201705/12/201704
Z_EXAMPLEContact01/04/202002/04/202011
Z_EXAMPLEContact08/04/202008/04/202002
Z_EXAMPLEReablement Plan10/04/202010/05/2020303
Z_EXAMPLEReview09/05/202009/05/202004
Z_EXAMPLEShort term service11/05/202016/06/2020365
Z_EXAMPLESwifts call out17/07/202017/07/202006
Z_EXAMPLESwifts call out28/07/202028/07/202007
Z_EXAMPLESwifts call out30/08/202030/08/202008

 

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
dujhe_0-1652882319017.png

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

1 ACCEPTED SOLUTION
PaulOlding
Super User
Super User

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])

 

PaulOlding_0-1652891381232.png

 

View solution in original post

2 REPLIES 2
dujhe
Regular Visitor

AH that makes sense now!

Thank you so much as this was driving me mad.

PaulOlding
Super User
Super User

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])

 

PaulOlding_0-1652891381232.png

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors