How To Create A Composite Model In DAX Studio

In this tutorial, you’ll learn how to create a composite model and use DirectQuery on a Power BI dataset or in Azure Analysis Services.

The previous tutorials have discussed the MEASURE, COLUMN, and TABLE keywords. In some specific cases, the DAX engine automatically writes these keywords even if you didn’t explicitly use them. You’ll learn how to mimic this scenario and understand what’s happening behind the scenes.

Creating A Composite Model: Getting Started

In this demonstration, a view is already created for the Date table. Select the code and execute it.

composite model

You can see that it results in a simple Date table which has the Date, Month, and Calendar Year columns.

Next, publish the data model to the Power BI Service.

composite model

Go to Power BI. Click Get Data and then Power BI datasets.

Choose the Dates Table and then click Create.

Open the Relationships view.

composite model

Create a relationship between the Sales table and the Dates table based on the Sales’ Order Date and the Dates table’s Date. You can do this by dragging Date to the Order Date. Wait for Power BI to create the relationship. Once done, click OK.

You can see that a limited relationship between the Sales Table and Dates Table has been created. A limited relationship is denoted by the symbol below.

composite model

Creating The Query For The Composite Model

Go to the Fields pane, open the Sales Table and create a New Column.

In this new column, write a simple measure. For this example, the new column shows the Order Year.

composite model

Once done, drag and drop the new column into the Power BI canvas.

Convert this into a table and make sure that the Order Year column is used as a row.

composite model

Next, drag and drop the Date from the Dates table. The Date should also be assigned as a row.

composite model

Click on the drop-down arrow for Date and select Count. This will count the number of values in a particular year.

Currently, the table shows the same count value for each year.

This is because the relationship between the Sales table and the Dates table is unidirectional. So, a filter from the Sales Table isn’t applied to the Dates Table.

To fix this, create a new measure.

composite model

This measure will CALCULATE the number of counts in the Dates Table. It will then modify the nature of the relationship using the CROSSFILTER function. The BOTH function ensures that the filter travels both ways.

Drag and drop this new measure onto the table and delete the Date column.

Go to the View tab and click Performance Analyzer. Once the Performance Analyzer pane appears, click Start recording and then Refresh visual.

composite model

Copy the table’s query and go back to DAX Studio.

Viewing The DAX Query & DirectQuery

When you paste the code, a dialogue box will appear asking which queries you want to paste.

Since this is connecting to the Power BI data set using the DirectQuery option, click the Both DAX and Direct Query option.

The code contains two sections. The top portion shows the DAX query executed because of the local model.

composite model

At the bottom, you can see the remote model in the Power BI Service which is executing the DirectQuery.

composite model

This gives you a visual of what’s happening with your DAX code. The engine is trying to create a table using the TABLE keyword. So even if you didn’t use the TABLE and COLUMN keywords to create queries, the engine uses them behind the scenes.

***** Related Links *****
Format DAX Codes Using DAX Studio’s Special Features
Configure Query Settings In Power BI DAX Studio
Connect DAX Studio To Power BI And SSAS


This tutorial shows a basic example of how composite models work in DAX Studio. This is a good representation of what happens behind the scenes.

In this example, the DAX engine uses the TABLE and COLUMN keywords to populate the result of the matrix or visual. So even if you didn’t explicitly use these keywords, the engine is using them behind the scenes to give you the result you need.

All the best,

Enterprise DNA Experts

Enterprise DNA Power BI On-Demand

The Latest

If you’ve been working with Python for data analysis or machine learning, you’ve likely come across NumPy arrays. They’re a powerful tool for handling numerical data, but sometimes, the data…

How to normalize a numpy array a quick guide.

Multiplying lists in Python is a common operation when performing mathematical computations or solving problems in data manipulation. There are multiple ways to achieve this task, depending on your requirements…

How to Multiply Lists in Python: A Simplified Guide

If you’ve been wrestling with Python lists and wondering how you can save them as a neat CSV file, you’re in the right place. One of the most common tasks…

How to Write a List to CSV in Python

Do you need to write an essay on the fall of the roman empire with accurate citations but have no time to find them? Are you tired of painfully having…

What is Caktus AI: A comprehensive overview

Pandas is a widely used Python library for data manipulation and analysis. One essential functionality that pandas can provide you is the ability to modify the structure of a dataset….

Pandas Drop Index: Efficiently Remove DataFrame Rows or Columns

Working with strings is a common task in Python. You often need to figure out whether a string contains another string. The simplest methods to check if a Python string…

7 Ways to Check if a Python String Contains Another

Ever found yourself knee-deep in Python files, struggling to import a module from a parent directory? If so, you’re in good company. Python’s import system can sometimes feel like a…

Python Import from Parent Directory: A Quick Guide

Data is the backbone of businesses these days, and having proof that you know how to handle and make the most out of data is a big deal in the…

Microsoft DP-500

In programming, you may encounter situations where a variable does not have a value. These instances are commonly represented by a special value known as null. Many programming languages, such…

Null Python: 7 Use Cases With Code Examples

Truncating a floating-point number in Python is a common operation that many programmers encounter in various applications. The process involves removing the decimal place of a float, leaving only the…

Python Truncate Flow: A Streamlined Approach for Efficient Code Execution

OpenAI’s ChatGPT tool has taken the world by storm and has been at the forefront of revolutionizing the way we generate content, do research, and even create code. And just…

Is ChatGPT worth it, let's work it out.

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and easy to maintain. The Python programming language comes with a…

Python Inline If: Simplify Your Conditional Expressions

Load More