DAX is HARD
Another Friday, another day of infinite struggle. To say DAX is not beginner friendly would perhaps be a huge understatement, and while there was a public holiday between days of training, it was still just as brutal. Fortunately, in the mass of information that I had problems processing, there was one thing that was actually both very useful and very easy to grasp; relationships.
In the simplest terms imaginable, relationships are how various tables join to a larger table via primary and foreign keys. What exactly are keys? They’re simply just the technical term for a how you can uniquely tag every single row in a table. So in a table showing purchases by customers from a store for example, the key could be something like the Order ID of each transaction, as there should ideally be no duplicate Order IDs for two different transactions if this store is doing business correctly.
The keys then join the main table to other smaller tables, from which there may be extra information and details. So in the example above, maybe there is an Orders table that is linked by the Order ID, showing a more detailed breakdown of items, prices, item codes, etc. The relationship here, is how the two tables are joined, via Order ID. There’s more to this, but that’s the quickest and simplest way I can explain what a relationship is how to use it.
The 2 functions I’d like to highlight today are USERELATIONSHIP and CROSSFILTER since they’re the 2 that I’ve found to be the most useful in what they can do.
USERELATIONSHIP and CROSSFILTER
One key thing about relationships between tables is that there can only be one ACTIVE relationship at any given time between 2 tables. This means that though I’m joining the Orders and Sales tables on Order ID, and Item ID, I can only have one of them be in effect at one time. This means that I anything I filter to on the Orders table will automatically apply itself to whichever column the relationship is joining the 2 tables on. Now, its probably better for any other visuals and charts that these relationships be left as is, and there’s always the risk that changing which relationship is active will completely crash some things. This is where the USERELATIONSHIP function comes in clutch.
The syntax of the function: USERELATIONSHIP(<columnName1>,<columnName2>) is extremely easy to use, and basically turns on that inactive relationship for that specific measure or calculation you need it to be active, without compromising the actual data model itself in the backend. This is fantastic for one-off calculations that need to be made, and can be extremely useful in the right situations.
The other function I’d like to discuss is CROSSFILTER, which changes and specifies the direction a relationship is flowing in. Relationships flow in a direction that is denoted in the data model by an arrow, and dictates which way filters would be applied. This is particularly useful for when you want to filter by a specific column, but the relationship is structured in a way that it isn’t possible, and like above, changing it could result in some pretty drastic errors. Crossfilter solves that by temporarily allowing a different direction for that measure only, much like USERELATIONSHIP’s temporary activation of an inactive relationship. The syntax for CROSSFILTER is CROSSFILTER(<columnName1>, <columnName2>, <direction>) and what this allows you to do is just temporarily enable a specific relationship in a direction that isn’t connected within the data model or something that needs to be temporarily used in the opposite direction. This is especially great in creating one-off visuals and sheets that need this relationship without completely reconfiguring the data model, and is extremely useful when trying to display something that normally will error out or return a blank.
