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

Advertisements

Consolidating time series metadata for users in Tabular

Background

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 DaxPatterns.com as well as other articles on SQLBI.com).

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.

Overview

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’ )
        ),
    TOTALYTD (
        [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.

Solution

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 :=
SWITCH (
    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!