Using a single calendar with multiple date fields in a fact table dynamically in PowerBI (with a twist)

Photo by Bich Tran on Pexels.com

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…

Photo by Min An on Pexels.com

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…

Photo by SHVETS production on Pexels.com

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?

Photo by Image Hunter on Pexels.com

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…

Using PowerQuery to automatically clean column names in PowerBI

Photo by Suzy Hazelwood on Pexels.com

What’s your favourite naming convention for tables or columns? Pascal case? Camel case? Snake case? Kebab case? Well as a developer it could be any of them depending on your preference, or even a combination. However, just pause for a moment and take off your developer hat. Now, imagine you’re a manager viewing a report, would you be expecting to see Pascal case, camel case or some other case on that report anywhere? Probably not, I mean imagine a report with a title of ‘this_is_my_report‘, that wouldn’t look very professional would it?

What you want to see is nice clear English names for all your columns and tables. Obviously we could solve this by just renaming columns manually. So, for example ‘some_field_in_some_table‘ would become ‘Some field in some table‘ or ‘Some Field In Some Table‘ (depending on how you like to capitalise words). However, this would of course become a pain in the butt if you’ve got loads of tables and columns. Luckily though, we can use PowerQuery here to help us out a bit…

Photo by Markus Spiske on Pexels.com

We’ll use a couple of the splitter functions here, first the Splitter.SplitTextByCharacterTransition function to handle Pascal case where we’ve got transitions from upper case to lower case. We’ll also need to use another function too, the Splitter.SplitTextByAnyDelimiter function. This will handle any delimited text that’s using separators (i.e. snake case or kebab case). Using a combination of both these functions we should be able split apart any text into groups of characters, depending on certain criteria. So with this in mind, lets just create a little function to do this text cleaning. We’ll start simple then improve on this later:-

let
    SplitFunction = (TextToSplit) => let
        // Another function to split non-delimited text
        SplitNonDelimitedText = (x) => Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(x),
        
        // Another function to split delimited text
        SplitDelimitedText = (x) => Splitter.SplitTextByAnyDelimiter({"_", "-", " "})(x),
        
        // Split the supplied text
        SplitResult = if Text.Contains(TextToSplit, "_") 
            or Text.Contains(TextToSplit, "-")
            or Text.Contains(TextToSplit, " ") 
            then SplitDelimitedText(TextToSplit)
            else SplitNonDelimitedText(TextToSplit)

        in 
        SplitResult
    
    in
    SplitFunction

Running this function with a parameter value of say either ‘ExampleText’, ‘Example-Text’ or ‘Example_Text’ produces the following output. Nice, just what we need:-

{"Example", "Text"}
Photo by Pixabay on Pexels.com

Ok lets build on this a little by cleaning and capitalising the text correctly. Using the Text.Clean function to remove any dodgy control characters (unlikely, but…) and Text.Proper to capitalise. Then finally a bit of code to get the column names from the supplied table and replace each of them with our ‘cleaned’ names. This is what we end up with:-

let
    CleanTableColumnNames = (TableToFormatColumnNames as table) => let                
        // Create a little function to split and clean the text
        CleanColumnName = (ColumnName) => let
            // Another function to split non-delimited text
            SplitNonDelimitedText = (x) => Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"})(x),
            
            // Another function to split delimited text
            SplitDelimitedText = (x) => Splitter.SplitTextByAnyDelimiter({"_", "-", " "})(x),
            
            // Split the supplied text
            SplitResult = if Text.Contains(ColumnName, "_") 
                or Text.Contains(ColumnName, "-")
                or Text.Contains(ColumnName, " ") 
                then SplitDelimitedText(ColumnName)
                else SplitNonDelimitedText(ColumnName),
                
            // Now recombine any parts using space as a word separator
            ParsedName = Text.Combine(SplitResult, " "),

            // Finally clean the text so we get nice capitalisation
            NewColumnName = Text.Clean(Text.Proper(ParsedName))

        in 
        NewColumnName,
                
        // Get the existing columns names from the table
        ExistingColumnNames = Table.ColumnNames(TableToFormatColumnNames),

        // Loop through each column name in the table and build new 
        // 'clean' names using the little split function we defined above
        NewColumnNames = List.Transform(
            ExistingColumnNames,
            each CleanColumnName(_)
        ),

        // We need to create a new list of lists with each original column
        // paired with its replacement column name
        OldAndNewColumnNamePairs = List.Zip({ExistingColumnNames, NewColumnNames}),

        // Now finally we can use the renamecolumns function to rename 
        // the columns in the supplied table with our replacement names ;-)
		RenamedColumns = Table.RenameColumns(TableToFormatColumnNames, OldAndNewColumnNamePairs)

    in
    RenamedColumns

in
CleanTableColumnNames

Now you can just use this function to apply to your table as just another step and watch the column names magically change to nice readable text. This will be much friendlier and professional looking than Pascal, snake or kebab case column names. Especially when designers are dragging these fields onto visualisations or slicers. Anyway that’s all for this one, hope someone finds this useful…