Tableau to Power BI: EXCLUDE Level of Detail Expressions

In a recent post on Tableau level of detail (LOD) expressions, I explored a method to translate FIXED to DAX in Power BI. Continuing that theme, I’ll now attempt to show an equivalent for the EXCLUDE keyword. While EXCLUDE and INCLUDE are not as common as FIXED in my experience, I’ve started down the LOD expression road and hope to finish this series in three parts for FIXED, EXCLUDE, and INCLUDE.

Continuing with Tableau’s examples from the Top 15 LOD Expressions post, here is the first step toward the final visualization in #6 – Comparative Sales Analysis. It builds a Sales of Selected Category calculation using EXCLUDE. Note how the subtotal for the selected category on the left appears for all categories on the right. EXCLUDE is used to obtain that subtotal.

Tableau Calculation to DAX

To “translate” FIXED to DAX, I used the ALLEXCEPT function. For EXCLUDE, I’ll use the ALLSELECTED function, which helps obtain the subtotal by removing the appropriate filters. ALLSELECTED takes either a column or an entire table as a parameter. For this example, the dataset focuses on obtaining the total for selected Categories, so I include a column reference in this case.

The original Tableau LOD expression for Sales of Selected Category is:

{ EXCLUDE [Category] : SUM([Selected Sales]) }

Note how the original example has an intermediate Selected Sales calculation, which relies on a parameter being passed for the selection:

The measure that I crafted in DAX appears below, and I use Sales instead of creating a separate Selected Sales measure. I will later click on the left visual in Power BI to select a category rather than use a parameter and intermediate calculation like in the original example. This effectively gives selected sales via a different route.

Sales of Selected Category = CALCULATE(SUM('Table'[Sales]), ALLSELECTED('Table'[Category]))

The original Tableau example has visuals for Sales and Sales of Selected Category in the same worksheet. In Power BI, there is no current ability to facet plots like this in the same visual, so you need to add two separate visuals to the report canvas.

Selecting a single category such as Computer Peripherals on the first visual changes the values on the second visual. It displays the selected category’s subtotal for all categories. Power BI’s default behavior is to highlight those subtotal values on the second bar chart but keep showing the original total (and reduce the opacity). For selections on the first bar chart, you can see the highlighted total in the tooltip on the second chart even though the data label disappears. Ultimately, this is a first step toward using the LOD to build a more complicated visual in the original Tableau example. The calculated value is more important than how the visual happens to display.

Even though we care more about the subtotal value than the visual in this case (because it’s an intermediate step toward creating a final visualization), it is possible to get the original Tableau view in Power BI. A single Category subtotal can appear for all of the categories rather than highlight against the overall total. I’ll give a Gold Star to anyone who figures out how to recreate the visual below in Power BI.

If it seems like this is too easy and that every Tableau expression has a quick equivalent in Power BI, it certainly gets more complicated. These first two LOD expressions happen to be fairly straightforward examples to translate for Tableau users who are getting started in Power BI. In future posts as well as a potential eBook for BlueGranite, I’ll look at more complex recreations based on questions I’ve received from trainees and clients. If you have any suggestions based on your experience working with either software, feel free to leave a comment on this blog post.

PREVIOUS: Learn how to “translate” a FIXED LOD expression to DAX – Tableau to Power BI: FIXED Level of Detail Expressions