Tableau LODs converted into Power BI Dax – Part 1

Hello world!

I’m starting a new blog series where I will converting Tableau LODs into Power BI Dax equations. I really enjoy challenges and I wanted to see if I was good enough to take this on. I enjoy using Tableau and Power BI and I do not care what tool I use as long as it gets the job done! My ultimate professional goal in life is to make sure my customer is satisfied with their results and that I am solving a problem. If I have the ability and capacity to learn new tools and skills then I’ll always take on the challenge to get better.

I’ve always been fascinated by all of the creative calculations that people are capable of making with Tableau LODs and Power BI Dax. If you are new to Tableau and Power BI then I’ll give you a brief overview of what these terms mean.

Tableau: Level of Detail expressions (also known as LOD expressions) allow you to compute values at the data source level and the visualization level. However, LOD expressions give you even more control on the level of granularity you want to compute. They can be performed at a more granular level (INCLUDE), a less granular level (EXCLUDE), or an entirely independent level (FIXED).

Power BI: Data Analysis Expressions (DAX) is a formula expression language used in Analysis Services, Power BI, and Power Pivot in Excel. DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models.

Earlier this year I ran across a blog post from the The Flerlage Twins (aka Tableau Gurus) where they highlighted 20 Uses for Level-of-Detail Calculations.

I reached out to Ken to see if he was ok with me attempting to convert his LODs into DAX and he gave me the green light (Thanks again!). I’m using the samplestore dataset from Tableau and my goal is to get the exact same results that were posted in the blogpost.

If you are new to Power BI then I recommend researching the Calculate function because it will be used a lot in my DAX calculations.

Get a Single Aggregate

The goal for this was to get the overall max value. We want to show the max sales across ALL orders without being specific.

Tableau	Power BI
// Highest sales value for any given record. {FIXED :  MAX([Sales] )}	//Highest sales value for any given records CALCULATE( MAX(‘Orders'[Sales]), ALL(‘Orders’))

Isolate a Specific Value

The goal for this is to get a specific value so that we can do comparisons across other dimensions. In this example, we want to identity the latest date our customer purchased something from the “Technology” category

Tableau	Power BI
// Get last date on which customers purchased technology. {FIXED [Customer Name]: MAX(     IF [Category]=”Technology” THEN         [Order Date]     END )}	//Get last date on which customers purchased technology. CALCULATE(max(Orders[Order Date]) , ALLEXCEPT(Orders,Orders[Customer Name]), Orders[Category] = “Technology”)

Get Related Data

My journey with parameters started in Tableau and they are REALLY powerful for user interaction. I had a bit of a learning curve with Power BI parameters (specifically text parameters) because they have a different meaning in comparison to Tableau. Power BI has native What-If parameter capabilities, but it only works with numeric values. I learned how to use Disconnected Tables in Power BI in order to get the same parameter functionally that I was used to in Tableau.

Tableau	Power BI
Selected Customer Sales // Sales for the customer selected in the parameter. {FIXED : SUM(     IF [Customer Name]=[Selected Customer] THEN         [Sales]     END )}	Selected Customer Sales //Sales for the customer selected in the parameter. var __parameter = SELECTEDVALUE(Parameter[Customer Name]) return CALCULATE(sum(Orders[Sales]) , ALLEXCEPT(Orders,Orders[Customer Name]), Orders[Customer Name] = __parameter)

Below are two videos that show the parameters working in action