SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 () )


Jared Zagelbaum's Blog

Jared is a highly experienced IT professional, specializing in the Microsoft Data Platform suite of technologies since 2007. He expertly leads projects and teams through enterprise solution design and implementation for data management, analytics, and information delivery. Jared regularly speaks at user groups and events across the Midwest, as well as blogging and providing technical review for SQL Server publications. He is a Microsoft Certified Solutions Expert (MCSE) in Business Intelligence, and a graduate of the University of Florida with a BS in Finance, Accounting and Economics.

Comments

Leave a comment on the original post [jaredzagelbaum.wordpress.com, opens in a new window]

Loading comments...