Using Existing DAX Magic to Perform a DistinctX in Power BI

In this video, Mitchell Pearson demonstrates how to write more complex DAX (Data Analysis Expressions) in Power BI by connecting to another table, retrieving a distinct list of values, and combining them. This video offers a step-by-step walkthrough for creating a new column in Power BI that aggregates task descriptions from a related table and removes duplicates using DAX functions. The session covers two main approaches to solve this problem, providing valuable insights into working with DAX for intermediate and advanced Power BI users.

Solving a Customer Problem Using DAX

Mitchell begins by introducing a simplified version of a real-world problem from a customer. The customer had a table with order numbers and concatenated task descriptions, and they needed a way to display the distinct task descriptions without duplicates. Mitchell demonstrates how to achieve this by using DAX functions in Power BI.

Step 1: Retrieving Task Descriptions with RELATEDTABLE

To begin, Mitchell uses the RELATEDTABLE function to retrieve related rows from the Order Details table and concatenate the task descriptions using CONCATENATEX. While this retrieves the task descriptions, it doesn't yet solve the issue of duplicates.

Step 2: Removing Duplicates with DISTINCT

Next, he introduces the DISTINCT function to remove duplicate task descriptions. By using SELECTCOLUMNS to select only the task description column, Mitchell applies the DISTINCT function and successfully removes duplicate descriptions before concatenating them back together.

Alternative Approach: Using SUMMARIZE

As an alternative, Mitchell demonstrates the use of the SUMMARIZE function, which implicitly performs a distinct operation, simplifying the process. This method groups the task descriptions and removes duplicates without the need for an additional distinct step.

Conclusion

In this video, Mitchell provides multiple ways to solve the problem of retrieving and displaying distinct task descriptions in Power BI using DAX. Whether using DISTINCT with SELECTCOLUMNS or leveraging SUMMARIZE, both methods demonstrate the flexibility of DAX for solving complex problems. Mitchell encourages viewers to practice and experiment with these techniques to improve their DAX skills.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on DAX, Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.