Understanding DAX Context Transition Challenges

Explanation

The issue seems to lie with the context transition in your provided DAX code.

The context transition refers to the process of transforming row contexts into an equivalent filter context, in DAX. In your DAX formula, SUMX is producing a row context, and CALCULATE is introducing a context transition. Moreover, the VALUES function is used in the table argument for the ADDCOLUMNS function, which itself is nested in SUMX.

We observe that this code may correctly calculate total appointments by address (and lower levels), but it tends to "lose" the defined filter context when working with higher hierarchical levels (Region, Sub Region, and Facility).

The reason behind this may be found in the ALLSELECTED function being applied to the whole 'Rooms' table. This function will preserve user-applied filters from outside the current visual (the slicers or cross-highlighted visuals for example), but will ignore any internal filters applied by the visual itself.

For example, when your visual applies a filter to only display the totals for a particular geographic region - the ALLSELECTED function ignores this filter and returns the total appointments for the whole address list, creating aberrations when operated at higher hierarchical levels.

Solution

Instead of using ALLSELECTED('Rooms'), the simple ALL('Rooms'[Rooms Address]) function should limit the scope of the calculation to addresses and prevent the repetition of total appointment figures at higher levels.

Your improved DAX formula would then read as:

Testing Appt by Address = 
SUMX (
    ADDCOLUMNS (
       VALUES('Rooms'[Rooms Address]),
       "A", CALCULATE([Number of Appointments] ,ALL('Rooms'[Rooms Address]))
    ),
    [A]
)

Please test this solution, and respond if you have any further queries or encounter any issues.

Note: When working with complex hierarchical data, it might be beneficial to create a separate measure for each hierarchical level to ensure accurate results. Aggregating correct values on each level independently and then connecting them with hierarchies is a potential technique in such cases.

Additional Resources

For deeper understanding of Context Transition, I'd recommend the "Mastering DAX" module in the Enterprise DNA Platform. This will enable you to decipher the complexities of context transition and how to optimally utilize it for sequential data manipulations in Power BI.