How To Set Up Power BI New Composite Models

After watching the new video from SQBI’s guys Unboxing new Power BI composite models I decided to try it.

Here is a quick guide on the steps to follow in order to set up a composite model.

Enable the Preview feature DirectQuery for Power BI datasets and Analysis Services.

In the Options configuration window in Power BI Desktop we need to check the “Power BI datasets and Analysis Services” Preview feature. After clicking OK,  Power BI requires to be restarted.

options with steps

Connect to the Power BI dataset to be used via Direct Query.

This creates a Live Connection with a model in the Power BI service. First click on the “Power BI datasets” button and then choose the Workspace. 


Add a local model and transform the Live Connection to Direct Query

Click on the “Transform Data” button and then “Add a local model”

Transform Data Direct Query connection

And that’s it

its’ now possible to add new tables using Power Query or DAX and to create calculated columns from the tables context menu in the Fields panel.




Variables in DAX and Common Mistakes

This post purpose is to show two common mistakes that happen when using DAX variables.

Variables in DAX are a powerful tool that improves the readability of DAX code. But their name is misleading, since DAX variables behave like constants.

The syntax to define a DAX variable is straightforward:

VAR myVariable = ... a DAX expression ...
RETURN ... a DAX expression using myVariable ...

For instance, this is valid DAX code, where we define the variable TotalSales and later we use it

[% Sales] = 
VAR TotalSales = CALCULATE( [Sales], REMOVEFILTERS( 'Date' ) )
RETURN DIVIDE( [Sales Amount[, TotalSales )

Once a variable has been declared, it cannot be changed like in a conventional programming language, where variables can be assigned multiple times.

To see what are the two common mistakes that newbies do when using variables, we create two tables in Power BI Desktop using this DAX code

Date = 
    CALENDAR( "2020-01-01", "2020-12-31" ),
    "Month", FORMAT( [Date], "mmm" ),
    "Month Number", MONTH( [Date] )

Sales = 
VAR MinDate =
    MIN( 'Date'[Date] )
        GENERATESERIES( 1, 10000 ),
        "Date", CONVERT( MOD( [Value], 365 ) + MinDate, DATETIME ),
        "Amount", MOD( [Value], 100 )

Then we create a one to many relationship between ‘Date'[Date] and Sales[Date] and we set the sort by column of ‘Date'[Month] to ‘Date'[Month Number].

This is our model:

We can create the measure [Sales Amount]

Sales Amount = SUM( Sales[Amount] )

And a matrix visual to show the [Sales Amount] per Month

Now we want to write a measure to compute the percentage of the sales over the total, and to do so we write this DAX code

% Sales Amount Wrong = 
VAR SalesAmount = [Sales Amount]
VAR TotalSalesAmount =
    CALCULATE( SalesAmount, REMOVEFILTERS( 'Date' ) )
    DIVIDE( SalesAmount, TotalSalesAmount )

But when we add this measure to the matrix visual we get this result

We have 100% per every month, which is wrong. Our mistake was to assume that the SalesAmount variable would be evaluated inside the CALCULATE with the modified filter context.

The fact is that once SalesAmount is defined, it contains the value for the current row, and when it is referred later it behaves like a constant. For instance, in January SalesAmount assumes the value of 42690, therefore the following CALCULATE behaves like if it was written

VAR TotalSalesAmount =
    CALCULATE( 42690, REMOVEFILTERS( 'Date' ) )

The correct code for this measure uses the [Sales Amount] measure instead

% Sales Amount = 
VAR SalesAmount = [Sales Amount]
VAR TotalSalesAmount =
    CALCULATE( [Sales Amount], REMOVEFILTERS( 'Date' ) )
    DIVIDE( SalesAmount, TotalSalesAmount )

And now the result is correct

The second mistake is less evident, since it happens when we define a variable containing a table.

We want to add a card visual to show the number of Sundays of January. We think it is a good idea (but it isn’t) to first prepare a table with just the Sundays and later to count its rows with a filter on January. So we write this code

# Sundays in January Wrong = 
VAR SundaysTable =
    FILTER( ALL( 'Date' ), WEEKDAY( 'Date'[Date] ) = 1 )
    CALCULATE( COUNTROWS( SundaysTable ), 'Date'[Month Number] = 1 )

And we find out that January has more Sundays that days

This is because the SundayTable in CALCULATE is a constant, like if it was written with literals: { { “2020-01-05”, 1, “Jan” }, { “2020-01-12”, 1, “Jan” }, … { “2020-12-27”, 12, “Dec” } }, therefore it is not affected by the filter context modified by CALCULATE.

But since the columns in table variables keep the data lineage, table variables can be used as filter arguments and the previous measure can be fixed like follows

# Sundays in January = 
VAR SundaysTable =
    FILTER( ALL( 'Date' ), WEEKDAY( 'Date'[Date] ) = 1 )
    CALCULATE( COUNTROWS( 'Date' ), 'Date'[Month Number] = 1, SundaysTable )

And now we see the correct number of 2020 January Sundays.

Variables are very useful and once we learn to avoid these mistakes we’ll use them to better organize our code, as a support for debugging and also, in some cases, as a tool to optimize our measure performances.

The DAX code in this post was written with the purpose to be as simple as possible to show the common mistakes.

The pbix file can be downloaded from my github profile