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:

https://en.wikipedia.org/wiki/Bitwise_operation

  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.

Example:

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

Solution:

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] )
        1,
    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] )
        1,
    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 () )