Monthly Archives: February 2021

Ambiguous model and USERELATIONSHIP

When a data model diagram contains cycles it’s ambiguous, because more than one path exists from the same two tables. When I began writing DAX code I thought that in case more than one path connected the same two tables, the result was the intersection of the filters. I was wrong.

A few days ago I answerd a question on stackoverflow.com about what happens when USERELATIONSHIP is used with an ambiguous model and I realized that the proposed model was the simplest case of ambiguity: three tables and three relationships.

There are two relationships on the Date column and an inactive one on the Name column between Product and ProductHistory. The last relationship is inactive, because otherwise it would create an ambiguity on the propagation of a filter from ‘Date'[Date] to ProductHistory[Date]. What would be the right path to follow? From Date directly to ProductHistory or from Date to Product and then to ProductHistory?

With the Product[Name] –> ProductHistory[Name] relationship inactivated, it’s evident that a filter applied to ‘Date'[Date] directly propagates to ProductHistory without crossing the Product table.

This can be seen by executing this query in DAX Studio, that per each ‘Date'[Date] shows the corresponding ProductHistory rows, according to the active relationship:

EVALUATE
GENERATE(
    ALL( 'Date'[Date] ),
    CALCULATETABLE(
        ProductHistory
    )
)

The result shows matching dates, as expected, since the ‘Date'[Date] –> ProductHistory[Date] relationship is the active one

But what happens when activating the Product[Name] –> ProductHistory[Name] relationship with USERELATIONSHIP ?

  • Both relationship are used and the result is the intersection
  • Only the relationship specified with USERELATIONSHIP is used and the other one is ignored

The answer is the latter: DAX uses only the path containing the relationship specified with USERELATIONSHIP and ignores any other existing path. The result is that the ‘Date'[Date] -> ‘ProductHistory'[Date] relationship becomes the inactive one for the duration of the CALCULATETABLE where USERELATIONSHIP is applied.

This can be seen by executing this other query in DAX Studio

EVALUATE
GENERATE(
    ALL( 'Date'[Date] ),
    CALCULATETABLE(
        ProductHistory,
        USERELATIONSHIP ( 'Product'[Name], ProductHistory[Name] )
    )
)

The result shows that the dates don’t match between ‘Date'[Date] and ProductHistory[Date].

‘Date'[Date] matches with the Product[Date], as can be seen in the code used to generate the three tables:

Product = 
DATATABLE( 
    "Name", STRING, "Date", DATETIME, "Quantity", INTEGER,
    {
        { "A", "2020-01-01", 1 },
        { "B", "2020-01-01", 2 }
    }
)

ProductHistory = 
DATATABLE(
    "Name", STRING, "Date", DATETIME, "Quantity", INTEGER,
    {
        { "A", "2020-01-02", 10 },
        { "A", "2020-01-03", 20 },
        { "B", "2020-01-01", 30 }
    }
)

Date = CALENDAR( "2020-01-01", "2020-01-31" )

The sample files for this article can be found on my github

Adding a Static Table with Types in Power Query With M Language

SQLBI’s guys recently published the Youtube video Create Static Tables in DAX Using the DATATABLE Function, where they show how to create a static table in Power BI.
At first using Power Query Enter data interface and then using the DAX language.
They also show that the M code generated by Enter data contains the table definition as an unreadable (by humans) encoded string, and that the only way to change it is through its specific editor.

Actually, it’s possible to create a static table using M readable code, using the syntax explained in this Chris Webb’s BI Blog post Creating Tables In Power BI/Power Query M Code Using #table(), where one of the proposed syntaxes also allows to specify the data types.

The M code to create the Segments table is the following

#table(
    type table
        [
            #"Price Range"=text, 
            #"Min Price"=number,
            #"Max Price"=number
        ], 
    {
        {"LOW", 0, 100},
        {"MEDIUM", 100, 1000},
        {"HIGH", 1000, 999999}
    }
)

This code uses the #table() function, specifying the type as the first parameter and the data as the second one.

To enter this code in Power Query first create a Blank Query

Then open the Advanced editor

And finally write the code in the Advanced editor window

selecting Done closes the Advanced editor window and shows the generated table

the #table() M function doesn’t have the same limitation of the corresponding DAX function, that only accept literals. With #table() variables are allowed.

Of course, defining static tables using M language has the disadvantage that a change triggers a refresh of the dataset.

The sample file can be downloaded from my GitHub