Distinctcount + sumx throws strange results

[JuanSombrero023]

Hi All,

I have been breaking my head around this one, hope somebody can help me understand. I have an issue with a Dax formula, I hope you don't mind I added an example in Excel PowerPivot rather than PBI...

Attached file contains data sample, 1 measure and a pivot table.

Result is as follows:

Can  somebody explain me the 122...I would totally expect 92 there...I am aware of how SUMX works, I am aware that totals are not always a sum of the above, and I feel like I am missing the obvious here, but I think I am in somewhat of a rabbit whole here 😉
Who can help me undertand.

Book1.xlsx

For the grand total, I would expect that VALUES(PropertyID) iterates over Property 1, counts 61 distinct dates...then Property 2, counts 31 disntinct dates, then sums them to 92...

Thx

Jan

[JuanSombrero023 · Accepted solution]

Appreciated alot man!

[OwenAuger]

Hi @JuanSombrero023 

Change your measure to:

Measure 1:=
SUMX (
    VALUES ( Table1[Occupancy_Data[PropertyId]]] ),
    CALCULATE (
        DISTINCTCOUNT ( Table1[Occupancy_Data[DateKey]]] )
    )
)

The short explanation is that in this case you need to wrap the 2nd argument of SUMX in CALCULATE to trigger "context transition". This converts the row context of the first argument of SUMX into equivalent filter context.

Without CALCULATE, with the PivotTable you have created, you won't see any difference on the rows of the PivotTable since each row's filter context is a single PropertyID already. However, at the total level, each iteration of SUMX has the same filter context (both PropertyID values) and ends up adding 61 DateKey values twice, giving 122 as the result.

See these articles for more explanation:

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

Regards