If you’ve navigated Tableau’s powerful Level of Detail (LOD) function, you know its complexity and potential for creating impactful visualizations. Now, whether you’re transitioning from Tableau to Power BI or need to excel in both platforms with limited Power BI skills, fear not! In this series, we’ll unravel the mysteries of applying Tableau’s three main LOD functions in Power BI.

We kick off with the FIXED LOD function, empowering your BI capabilities and streamlining your data visualization journey. Stay tuned for insights that will elevate your data game!

Using our favorite super store dataset, we want a simple visual that displays Customer ID, Order ID, Order Date as well as Earliest Order Date per customer, so we created a field called First Order Date using FIXED LOD.

{FIXED [Customer ID]: MIN([Order Date])}

Drag all the fields to rows, we got exactly what we need, and for each customer ID, we have the same First Order Date.

In Power BI, we can use ALLEXCEPT in DAX to create something same as the FIXED LOD in Tableau. The measure can be written this way:

First Order Date =
CALCULATE(
    MIN(Orders[Order Date]), 
    ALLEXCEPT(Orders, Orders[Customer ID])
)

Here is our result in Power BI:

One helpful note, since we were dealing with date type, we can also use FIRSTDATE/LASTDATE instead of MIN/MAX to get the same result.

What if you used multiple dimensions in the FIXED Lod? Simply add additional ALLEXCEPT functions as filters for CALCULATE in POWER BI. From :

{FIXED [Customer ID], [Product Name]: MIN([Order Date])}

To:

First Order Date =
CALCULATE(
    MIN(Orders[Order Date]), 
    ALLEXCEPT(Orders, Orders[Customer ID]),
    ALLEXCEPT(Orders, Orders[Product Name])
)

In this specific scenario, you may also explore utilizing Metrics instead of Tabular data without the need to create a new measure. When you drag and drop customer ID, order ID, and order date into their respective fields, an implicit measure is applied. This automatically provides you with the earliest date at the first level of hierarchy (customer ID), followed by order date for each individual order ID.

Word of warning though: Ensure that row subtotals are enabled to avoid having blank level one aggregations.

Translate LOD on Number field:

Now that you’ve grasped how to convert FIXED LOD to POWER BI DAX for date fields, let’s delve into another example. Suppose we aim to visualize total sales at both the state and region levels. We can achieve this by utilizing the FIXED SUM function for regional-level aggregation.

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

In Power BI, here is what we get with similar ALLEXCEPT formula:

Total Sales by Region = 
CALCULATE(
    SUM(Orders[Sales]),
    ALLEXCEPT(Orders,Orders[Region])
)

Stay tuned for our next articles on how to translate Exclude and Include LODs to Power BI DAX!