MIP Logo

How to use Tableau’s FIXED LOD in Power BI by DAX?

This post was originally published on The Data School blog between 2018 and July 2025, before our program was renamed to MIP’s Analytics Career Accelerator. References throughout this article to “The Data School” or “DS” all refer to what is now MIP’s Analytics Career Accelerator. The program, its people, and its commitment to launching outstanding analytics careers remain the same – just under a new name.

If you are having trouble viewing this article, please report it here. 

What is LOD?

LOD (Level of Detail) allows you to compute values at the level of data source and visualization. However, it gives you more control over the level of granularity you want to compute. In FIXED LOD, we can specify the level of granularity. This blog is written for people who have the basics of Tableau but are beginners to Power BI.

I will use the Sample Superstore dataset to show how to use Tableau’s FIXED LOD in Power BI. If you want to do the same thing as me, you can download the data from here.

 

What’s the LOD like in Tableau?

Let’s see the below screenshot. Sales are drilled down at Sub-Category Level by default.

 

Figure 1. Sales at Sub-category Level in Tableau

 

How can we calculate the sum of sales at the Category level? We can use FIXED LOD to deal with that.

Create a calculated field and put the below statement:

{ FIXED [Category]:SUM([Sales])}

Then you can get the following result.

 

Figure 2. Sales at Category Level by using LOD in Tableau

 

How to apply DAX to achieve the LOD in Tableau?

First, replicate the above process. Get the sum of sales at the Sub-category level.

 

Figure 3. Replication of Figure 1 in Power BI

 

To calculate the sum of sales at the Category level in Power BI, we can use ALLEXCEPT in DAX to do the same thing as FIXED LOD in Tableau. ALLEXCEPT removes all the context filters in the table except those that have been applied to the specified columns.

Right-click the Orders table and add a measure.

Write the below statement:

CALCULATE(
    SUM(Orders[Sales]),
    ALLEXCEPT(Orders,Orders[Category])
    )
Then you can get the same result.

Figure 4. Achieve LOD by DAX in Power BI

 

How to fix multiple dimensions in DAX?

In Power BI, you just need to add additional ALLEXCEPT functions as filters for CALCULATE to fix multiple dimensions.

Tableau Example:

{ FIXED [Category],[Product Name]:MAX([Order Date])}

 

Transform in Power BI:

CALCULATE(
    LASTDATE(Orders[Order Date]),
    ALLEXCEPT(Orders,Orders[Category],Orders[Product Name])
    )

 

If there are any problems, please feel free to point them out. Besides, you can reach out to me on LinkedIn. I will try my best to answer your questions about Tableau or Alteryx.

 

Share this post