Bitwise XOR in DAX

In T-SQL and other languages we have bitwise XOR natively supported; the T-SQL operator is simply ^. Here’s the explanation of bitwise XOR for those not familiar:

  1. Why would we want to implement this in DAX?
  2. How do we implement it?

1) I implemented XOR to ensure that only one table was being filtered from among a group of role playing dimension tables. This adds some validation around returning data to users when they may be using conflicting filter values.

2) The implementation is fairly straightforward. For any logical expression, we can implement a true/false condition, then return a value if only one condition is true.


I have a series of snapshot date filter tables for end of period values that all relate to a single snapshot fact table. The end date values in the filter tables are for week, month, and year for both Calendar and Fiscal period, for a total of six role playing tables (albeit with not exactly the same data, but some intersecting values). I want to remove the chance of rendering any values when more then one snapshot date is being filtered against facts by any of these tables.

XOR snapshot


So, first we create a few intermediate measures to implement our conditions. In our specific case, I want to make sure only one of the role playing snapshot tables is filtered to a single date.

Measure #1 – Check that only one snapshot date table is filtered:

SnapshotXORFilter :=
IF (
    ISCROSSFILTERED ( ‘Calendar Month Snapshot'[Calendar Month Snapshot Date] )
        ISCROSSFILTERED ( ‘Calendar Week Snapshot'[Calendar Week Snapshot Date] )
        ISCROSSFILTERED ( ‘Calendar Year Snapshot'[Calendar Year Snapshot Date] )
        ISCROSSFILTERED ( ‘Fiscal Month Snapshot'[Fiscal Month Snapshot Date] )
        ISCROSSFILTERED ( ‘Fiscal Year Snapshot'[Fiscal Year Snapshot Date] )
        ISCROSSFILTERED ( ‘Fiscal Week Snapshot'[Fiscal Week Snapshot Date] )
    TRUE (),
    FALSE ()

Measure #2 – Check that only one date value is filtered across all the tables:

SnapshotXORValue :=
IF (
    HASONEVALUE ( ‘Calendar Month Snapshot'[Calendar Month Snapshot Date] )
        HASONEVALUE ( ‘Calendar Week Snapshot'[Calendar Week Snapshot Date] )
        HASONEVALUE ( ‘Calendar Year Snapshot'[Calendar Year Snapshot Date] )
        HASONEVALUE ( ‘Fiscal Month Snapshot'[Fiscal Month Snapshot Date] )
        HASONEVALUE ( ‘Fiscal Year Snapshot'[Fiscal Year Snapshot Date] )
        HASONEVALUE ( ‘Fiscal Week Snapshot'[Fiscal Week Snapshot Date] )
    TRUE (),
    FALSE ()

Measure #3 – Combine into a single expression for easier reuse:

SnapshotRender :=
IF (
    [SnapshotXORFilter] && [SnapshotXORValue],
    TRUE (),
    FALSE ()

Now, for all measures in my snapshot table, I can check against the SnapshotRender value before rendering like so:

On Hand Qty :=
IF ( [SnapshotRender], SUM ( [OnHandCount] )BLANK () )

…or more generally

Semi Additive Measure :=
IF ( [SnapshotRender], Function ( [ColumnValue] )BLANK () )


Consolidating time series metadata for users in Tabular


DAX offers many powerful time intelligence functions that can be applied at virtually any level of a date / time hierarchy. There are also custom patterns available that can be implemented for week based and ISO 8601 calendars that will emulate the same functionality (case in point, see Time Patterns articles on as well as other articles on

An issue that arises with the implementation of time intelligence in DAX, however, is that the default behavior is to create a new metadata entry for every measure created in the model. This behavior can quickly propagate your cube metadata into something confusing and error prone to users.


As an example, consider a common requirement: YTD, QTD, and MTD measures. Assuming a standard Gregorian calendar, these measures could be implemented in the AdventureWorks 2012 Tabular Model as follows:

Note: QTD and MTD would use the same calculation with the exception of replacing TOTALYTD in the DAX formulas with TOTALQTD and TOTALMTD, respectively.

YTD Internet Sales Amount :=
IF (
    MIN ( ‘Date'[DateKey] )
        <= CALCULATE (
            MAX ( ‘Internet Sales'[OrderDateKey] ),
            ALL ( ‘Internet Sales’ )
    TOTALYTD ( [Total Internet Sales Amount], ‘Date'[Date] )

Extending the requirement to include comparisons against previous year, we could implement these measures as well:

PY YTD Internet Sales Amount :=
IF (
    MIN ( ‘Date'[DateKey] )
        <= CALCULATE (
            MAX ( ‘Internet Sales'[OrderDateKey] ),
            ALL ( ‘Internet Sales’ )
        [Total Internet Sales Amount],
        SAMEPERIODLASTYEAR ( ‘Date'[Date] )

If we save and browse our model with these definitions in place, we end up with this result:

Unconsolidated Excel Sample

Notice how MTD amounts incorrectly render at the quarter and year levels of the date hierarchy. Likewise for QTD at the year level as well. These issues are also repeated for the PY measures.

The “quick and dirty” solution to this problem would be to hide any values for *TD measures where an inappropriate date attribute is used in the pivot / report. This would eliminate the risk for errors in reports (or, more likely, the risk of the developer losing all credibility). However, there still remains a major unresolved issue with this approach: Every corresponding measure for which there is a *TD requirement would have metadata in triplicate. Users would have to remember to select MTD, QTD, or YTD versions of each measure from the model depending on which time intelligence function was desired for reporting. Likewise, a report with drilldown on the date hierarchy would basically end up being dumped to a spreadmart, or become a developer created / assisted task as the ad hoc layout of such a report from the cube would be 3x larger then necessary.


Enter the Switch() function. Adding a check against the date hierarchy context being filtered in the query, this function can consolidate and render the appropriate *TD measure while exposing only a single metadata entry in the model. We can now meet a common requirement to see cumulative amounts as such:

Note: PY Internet Sales Amount To Date measure would reference PY versions of the same measures

Internet Sales Amount To Date :=
    TRUE (),
    ISFILTERED ( ‘Date'[Day Of Month] ), [MTD Internet Sales Amount],
    ISFILTERED ( ‘Date'[Month Name] ), [QTD Internet Sales Amount],
    ISFILTERED ( ‘Date'[Calendar Quarter] ), [YTD Internet Sales Amount],
    ISFILTERED ( ‘Date'[Calendar Year] ), [YTD Internet Sales Amount]

The intermediate *TD measures can now just be hidden from client tools in the model, and used only as intermediate calculations by the measures implementing the Switch() function. This allows us to consolidate metadata and business rules into a single measure per time series like so:

Consolidated Metadata

Here’s how the data looks based on the Switch() rules used above (dates are filtered through to 5/25/2008 to show *TD functionality against PY):

Consolidated Excel Results

The Internet Sales Amount To Date and corresponding PY measures now show only the required cumulative totals at the appropriate level of the date hierarchy. Here it is MTD at day level, QTD at month, and YTD elsewhere. This can be easily changed to meet different requirements purely in the Switch() function.

Parting Thoughts

The nice part about this implementation is that it is naturally extensible thanks to DAX’s built in inheritance. Creating a single YOY cumulative calculation is simply:

YOY Internet Sales Amount To Date := [Internet Sales Amount To Date]- [PY Internet Sales Amount To Date]

…and so on, etc..

The DAX implementation of time series metadata is still not as nicely abstracted as Multidimensional yet, but there are alot of tools you can use to simplify tabular models to meet most user requirements. Hope this helps!

Let’s get started

Just back from PASS Summit 2014 and I am feeling inspired about stepping it up a notch and starting to give back to the great community that has provided me with the knowledge to build my career over the years. Hopefully, I’ll be able to help a frustrated person out there somewhere with a tidbit or two from some of the solutions I’ve created.

I’m primarily a business intelligence practitioner, which means I’m really all over the stack. You can expect to see posts here about cloud services (mostly Azure because I currently get free MSDN credits), Data Architecture / Modeling, Data Visualization (all the new stuff), SSIS / SSAS / SSRS (the old stuff), DAX & BISM (my new personal favorite), and whatever else comes out next week that makes this post sound old already.

I’m working on some interesting projects right now that I hope to have published patterns for in the near future (comparable sales logic in DAX and creating and maintaining a standard report template in SSRS). In the meantime, I thought I’d start like a real pro, and post some of the stuff I’ve done in the past again…

Monitoring scheduled report executions in SSRS using SSRS

Implementing SSO with per-user identity using Claims, SharePoint 2013 Business Intelligence Services, and Windows 2012 Server Web Application Proxy

As I mentioned before, really, all over the stack…