Tableau to Power BI: INCLUDE Level of Detail Expressions

In Tableau, INCLUDE level of detail (LOD) expressions are used to obtain values for dimensions that are not directly used in a visualization. This final LOD post focuses on adapting Tableau INCLUDE LOD expressions to DAX in Power BI. See previous posts on FIXED and EXCLUDE as well. With FIXED, I used the counterpart ALLEXCEPT function in DAX. With EXCLUDE, I used the ALLSELECTED function. For INCLUDE, I shift away from ALL-related functions and instead take a different angle.

As with the previous posts, I pulled an example from Tableau’s Top 15 LOD Expressions blog post. For INCLUDE, let’s consider #7 – Average of top deals by sales rep. For this LOD expression, we obtain an average sales by country based on maximum sales by sales rep. Since the visual does not directly involve Sales Rep, the INCLUDE calculation helps include the maximum for each sales rep.

{ INCLUDE [Sales Rep] : MAX([Sales]) }

Tableau INCLUDE LOD for Power BI - DataVeld

 

The visual then averages those maximum values.

Tableau INCLUDE LOD for Power BI DAX - DataVeld

 

Translating this over to DAX, how can we get an average of maximums?

Taking it step by step, the approach I chose focused on getting the maximums by sales rep. This is difficult to see mid-calculation in Power BI since the output is a table and not a measure. As a result, here is the result shown in DAX Studio. If you are not using DAX Studio and the excellent materials on DAX from SQLBI like The Definitive Guide to DAX, you should start. They will greatly help the process of getting to know DAX and will enhance your Power BI experience.

For this portion, I used the SUMMARIZE function to effectively group MAX sales by Sales Rep.

Tableau INCLUDE LOD to Power BI DAX - DataVeld

 

If you attempted to drop this as-is into a measure in Power BI, you would receive an error though.

Tableau INCLUDE LOD for Power BI DAX - DataVeld

 

It forms the core of what you can add in Power BI, but you then need to wrap the final aggregation around it. In Tableau, you can have the Largest Sales Deal by Rep calculation and then change aggregations dynamically in the visual. In Power BI, you have to code your aggregation into your measure. In this case, I used AVERAGEX to get the average of the maximum sales values.

Average Largest Sales Deal by Rep = AVERAGEX(
     SUMMARIZE('Sales by Rep', 'Sales by Rep'[Sales Rep], "Largest Sales Deal by Rep", MAX('Sales by Rep'[Sales])),
     [Largest Sales Deal by Rep]
)

 

Adding this measure to the visual like in the Tableau original, I see the results per country even though Sales Rep is not directly used in the visual.

Tableau INCLUDE LOD for Power BI DAX - DataVeld

 

There are other ways to approach Tableau’s INCLUDE as well. For example, it technically would work to use GROUPBY instead of SUMMARIZE in your measure. Read The Definitive Guide to DAX by Marco Russo and Alberto Ferrari (especially the note on page 262) to understand why GROUPBY may not be a great idea in this circumstance — especially if you have a large number of records.

GROUPBY Alternate - Do Not Use This = AVERAGEX(
    GROUPBY('Sales by Rep', 'Sales by Rep'[Sales Rep], "Largest Deal", MAXX(CURRENTGROUP(), 'Sales by Rep'[Sales])),
    [Largest Deal]
)

 

Maxim Zelensky (b/t) also mentioned VALUES in place of SUMMARIZE as another alternative. In the special case of one dimension, you can build a measure like this one.

Average Largest Sales Deal by Rep = AVERAGEX(
    VALUES('Sales by Rep'[Sales Rep]), 
    CALCULATE(MAX('Sales by Rep'[Sales]))
)

Once a more complicated LOD such as the following one with multiple attributes is involved, new users should find that SUMMARIZE is a more universal form. With DAX as with many things, there is a fine balance between teaching new users everything out of the gate or going with what is potentially more familiar and having them work toward optimization later.

{ INCLUDE [Sales Rep], [Product Line] : MAX([Sales]) }
Average Largest Sales Deal by Rep = AVERAGEX( 
    SUMMARIZE('Sales by Rep', 'Sales by Rep'[Sales Rep], 'Product'[Product Line], "Largest Sales Deal by Rep", MAX('Sales by Rep'[Sales])), 
    [Largest Sales Deal by Rep] 
)




Leave a Reply

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