Quick question about SUMX

[JustinDoh1]

I am trying to understand about SUMX.

I have uploaded my PBIX file here for reference.

I have a measure called "Sumx", and its code looks like this:

Sumx = SUMX(

                VALUES('Date'[DateFormat]), // Table

                CALCULATE

                (

                    DISTINCTCOUNT([ClientID]) // Expression?

                )

            )

By definition, it "returns the sum of an expression evaluated for each for in a table".

But why do we have table here 'Date'[DateFormat] which counts 1,336 rows? What is use of this? 

What is point VALUES('Date'[DateFormat]) in this case?

Thanks.

[VahidDM · Accepted solution]

@JustinDoh1 

SUMX works like this:
SUMX( Table, Expression ) goes row by row over the table, evaluates the expression for each row, then adds everything up.

In your measure:

Sumx = SUMX( VALUES('Date'[DateFormat]), CALCULATE(DISTINCTCOUNT([ClientID])) )

VALUES('Date'[DateFormat]) creates a one-column table of distinct dates in the current filter context. If you have 1,336 dates, SUMX will iterate 1,336 times.

For each date, CALCULATE enforces the context to that date only, then runs DISTINCTCOUNT([ClientID]).

SUMX adds up those daily distinct counts. That means if the same client appears on multiple days, they get counted multiple times (once per day). This is different from just DISTINCTCOUNT([ClientID]) which would count unique clients across the whole period once.

So the point of VALUES('Date'[DateFormat]) is to drive the iterationm, it forces SUMX to recalculate your measure per date, rather than over the whole set in one go.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[JustinDoh1]

@VahidDM Thank you for help and your explanation, but I am not clear with need for "filter context" on this case. I have uploaded a new PBIX file here for illustrations: 

All three measures result in same values.

First one (original):

Sumx = SUMX(

                VALUES('Date'[DateFormat]), // Table

                CALCULATE

                (

                    DISTINCTCOUNT([ClientID]) // Expression?

                )

            )

Second one: 

SumxNew = SUMX(

                VALUES('Date'[DateFormat]), // Table

                CALCULATE

                (

                    count([ClientID]) // Expression?

                )

            )

Third one:

ClientID_M = count('Table'[ClientID]) + 0

Why do we even bother with 

" VALUES('Date'[DateFormat]) " part if we could just use the third one?

Thanks.

[v-achippa]

Hi @JustinDoh1,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @VahidDM, @lbendlin and @FBergamaschi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[FBergamaschi]

The answer to these questions

___________

But why do we have table here 'Date'[DateFormat] which counts 1,336 rows? What is use of this? 

What is point VALUES('Date'[DateFormat]) in this case?

__________

Is tgat you can build your table as SUMX evaluates an expression on every riw of any table, it can be a model table or a virtual table like in this case

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[lbendlin]

Your 'Date' table has 1336 distinct values in the [DateFormat] column. That's what VALUES collects.  And then you use each of these values as a filter context for the CALCULATE.

[JustinDoh1]

@lbendlin Thank you for your help. Sorry. I am still not getting it. What does it mean by "And then you use each of these values as a filter context for the Calculate"?  Why do we need to even bother with VALUES('Date[DateFormat])? Thanks.

[lbendlin · Accepted solution]

Why do we need to even bother with VALUES('Date[DateFormat])? 

That would be a question for that column. If that column can serve as a primary key then you don't need VALUES. 

[VahidDM · Accepted solution]

@JustinDoh1 

SUMX works like this:
SUMX( Table, Expression ) goes row by row over the table, evaluates the expression for each row, then adds everything up.

In your measure:

Sumx = SUMX( VALUES('Date'[DateFormat]), CALCULATE(DISTINCTCOUNT([ClientID])) )

VALUES('Date'[DateFormat]) creates a one-column table of distinct dates in the current filter context. If you have 1,336 dates, SUMX will iterate 1,336 times.

For each date, CALCULATE enforces the context to that date only, then runs DISTINCTCOUNT([ClientID]).

SUMX adds up those daily distinct counts. That means if the same client appears on multiple days, they get counted multiple times (once per day). This is different from just DISTINCTCOUNT([ClientID]) which would count unique clients across the whole period once.

So the point of VALUES('Date'[DateFormat]) is to drive the iterationm, it forces SUMX to recalculate your measure per date, rather than over the whole set in one go.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube