Tableau to Power BI: FIXED LOD Expressions

Every once in awhile, I encounter questions from a trainee or client who is familiar with Tableau but who is getting started with Power BI. They have the ability to do X in Tableau and want to know its equivalent in Power BI. In some cases, it’s an easy answer, and in other cases, there is no answer. That’s part of the difficulty in comparing products at different stages in their lifecycles.

About three years ago, Tableau introduced Level of Detail (LOD) expressions in version 9. In Power BI terminology, a Tableau LOD changes the filter context. For Power BI readers who may not be familiar with LOD expressions, there are three keywords that help change that context: FIXED, INCLUDE, and EXCLUDE. Tableau has a whitepaper that explains each type as well as shows how they evaluate within a filtering hierarchy. There are also numerous blog posts from Tableau community members that help explain LOD expressions.

Oversimplified for this post, the FIXED LOD specifies an exact level of detail. In Power BI, one way to approach this is to use the ALLEXCEPT function to preserve a filter on a given column.

The example LOD I use in this post is from Tableau’s #5 New Customer Acquisition calculation from their Top 15 LOD Expressions blog post. The Tableau calculation is:

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

This sets a “fixed” value of the minimum order date for the associated Customer ID on each row.

Tableau FIXED LOD expression

For new Power BI users attempting to recreate this in DAX, an equivalent approach could be to create the following new measure:

CALCULATE(
    MIN('Table'[Order Date]), 
    ALLEXCEPT('Table', 'Table'[Customer ID])
)

Power BI ALLEXCEPT Tableau FIXED LOD

In this particular case, since you are working with dates, you could also use the FIRSTDATE function in place of MIN.

CALCULATE(
    FIRSTDATE('Table'[Order Date]), 
    ALLEXCEPT('Table', 'Table'[Customer ID])
)

Power BI ALLEXCEPT Tableau FIXED LOD

Here is the result in Power BI:

Power BI ALLEXCEPT Tableau FIXED LOD expression

Going a step further, you can used FIXED with multiple dimensions. In Power BI, simply add additional ALLEXCEPT functions as filters for CALCULATE.

{FIXED [Customer ID], [Product Name]: MIN([Order Date])}
CALCULATE(
    FIRSTDATE('Table'[Order Date]), 
    ALLEXCEPT('Table', 'Table'[Customer ID]),
    ALLEXCEPT('Table', 'Table'[Product Name]),
)

 

I’m not an expert when it comes to DAX, but there are plenty of great resources to help learn more for Power BI. What are some that I regularly reference? I recommend checking out Curbal’s DAX Fridays! video series on YouTube and reading material from Alberto and Marco as well as Matt Allington.

NEXT: Learn how to “translate” an EXCLUDE LOD expression to DAX – Tableau to Power BI: EXCLUDE Level of Detail Expressions




3 thoughts on “Tableau to Power BI: FIXED LOD Expressions

  1. I have this LOD in Tableau and wonder how to convert to Power BI ?

    { FIXED [Customer],(DATEPART(‘year’, [Job Date])*100 + DATEPART(‘month’, [Job Date])) : AVG([No of Car])}

  2. Hi

    I have this tableau calculated field :
    { FIXED [Customer],(DATEPART(‘year’, [Job Date])*100 + DATEPART(‘month’, [Job Date])) : AVG([No of Car])}

    Want to know how to convert to Power BI ?

    Thanks & Regard

  3. Hi Can u help me to convert this to DAX

    {FIXED
    [Dstr Name],[Product description],YEAR([Day Date]), MONTH([Day Date])
    :
    COUNTD(
    IF
    [Measure])]>0
    THEN
    [Product description]
    END
    )

Leave a Reply

Your email address will not be published. Required fields are marked *