Tag: DAX

Why am I covering this? It’s the age old arguement in PowerBI, do I have a single calendar or multiple calendars, one for each date in my fact table? Many people have already written posts on how to solve this issue, mostly either using the USERELATIONSHIP function in DAX or just by creating multiple (i.e. copies) calendars, one for calendar for each date… ok so all done then, post complete? Hang on, wait a sec, this is a little twist on the normal proceedings so stick with me for a moment, this might be useful… maybe…

First a little background and for anyone who remembers the old days of multidimensional SSAS, this was a problem usually solved using ‘true’ role playing dimensions. Role playing dimensions are, or were, one real dimension masquerading as several via multiple relationships to a fact table. This is different to how you would probably implement ‘role playing’ dimensions in PowerBI, your only choices really are physical copies (usually calculated tables) of the dimension (one for each date field in your fact table). Alternatively, using a single dimension with several relationships to the same fact table and then employing the good old USERELATIONSHIP function. See what I mean about this here in the Microsoft PowerBI documentation regarding star schemas and dimensional modelling. Unfortunately this nice feature from the old days of multidimensional SSAS was never implemented in SSAS tabular (i.e. the technology that sits behind PowerBI) so we’re left with the usual options talked about above. It’s mainly preference I guess, but if you’ve only got a few dates then creating a few calendars isn’t too bad and doesn’t bloat the data model too much or increase its size massively. However, if you’ve got a lot of dates fields in your fact table then this can quickly spiral out of control. I think most people would (at this point) just have a single calendar and (as detailed above) rely on the USERELATIONSHIP function. You’d probably then create several measures which just activate the relevant calendar relationship. Well, maybe we can simplify this by just using a single measure and also gain some other added value benefits…

This is going to work by using a combination of a SWITCH statement in the measure and also creating a static table with some hard coded values in. This table is going to list each of the names of the dates, it could then serve as a slicer or filter to allow someone to choose which date relationship to use and the SWITCH statement in the measure decides which relationship to activate. The data in this table could be entered manually (like below) or even defined in the data source (such as an SQL table), either way the principle is the same.

Now we can just create a single measure and use the SWITCH statement to figure out which date the user wants to use depending on the selection of the date name in the active date table. We can figure out which date has been selected by using the SELECTEDVALUE function. So the DAX for the measure becomes something a bit like this:-

Total Sales = SWITCH(
    SELECTEDVALUE('Active Date'[Date Name]),
    
    "Open Date",
    CALCULATE(
        SUM(MyFactTable[Some Value]),
        USERELATIONSHIP(
            'Calendar'[Date],
            MyFactTable[Open Date]
        )
    ),
    
    "Closed Date",
    CALCULATE(
        SUM(MyFactTable[Some Value]),
        USERELATIONSHIP(
            'Calendar'[Date],
            MyFactTable[Closed Date]
        )
    ),
    
    // If nothing selected, default to just use
    // the 'Open Date' relationship
    CALCULATE(
        SUM(MyFactTable[Some Value]),
        USERELATIONSHIP(
            'Calendar'[Date],
            MyFactTable[Open Date]
        )
    )
)

Not super clean code I admit, in a perfect world I’d want to just have a single CALCULATE statement then use the SWITCH statement inside it to just choose which relationship to use, that’d look much cleaner in my eyes. Unfortunately that’s not supported at the time of writing so we’ve got to live with the several CALCULATE statements. Ok, but what’s the benefit of all this?

Well, the main point of using this method is that we can now let the user choose which dates to use by using a slicer for example, like this:-

Another cool, but maybe not super useful feature, is that you can select all dates and show them on the same chart. I guess this could be helpful for some comparisons, maybe, or it could be too much overload – so use only where appropriate. To do this just drop the ‘Date Name’ field from the static table onto the ‘legend’ panel for the visualisation, then select all the dates on the slicer and hey presto:-

Ok there we go. That’s all for today from me, hope you found this interesting and possibly useful. I’ll be back with some more random tidbits soon…