I needed to quickly create a calculation that tested for the presence of a string in another column’s text values. There are two ways to solve this, but the first one is an interesting example that you can use in several situations. This solution plays on the true-false values of the DAX IF function in PowerBI.
The calculation would process a column based on the string containing a specific value. For example, “Jim’s Product” would be classified as a “Product” and “Cleaning Service” would be classified as a service. Logic would be processed on what group the values belonged to. I was creating a custom column in PowerBI Desktop and putting an if statement containing a DAX search function in the conditional portion.
Option 1: When is an IF statement true?
The below formula works, which really blew me away. The IF condition would get the numeric location resulting from the search function finding the first letter of the text it was looking for. This could be any number except zero.
SEARCH(<find_text>, <within_text>[, [<start_num>][, <NotFoundValue>]])
This should not work but does. The picture below evaluates to what you would expect, that a zero is false.
But as the below screen capture shows that any number not equaling zero evaluates to true.
So, you should be able to put in any function in the conditional portion of the IF statement that becomes a number. In this example, the DAX Search function gives you a numerical result.
Lesson Learned
Breaking one of my golden rules, I was trying to do everything at once.
Golden Rule #1 – When you have a complex formula, write it in segments to break it up and make it easier to debug. Once you have it working, then you can get more complex.
Once I started on this looking at the calculation, I remembered that this functionality is in the Query Editor. Creating a Conditional Column for the grouping made the final calculation far easier to write. Below are the steps to set this up.
Option 2: Using the Query Editor and Conditional Columns for Groupings
Pictured below is the screen that captures setting this up in PowerBI. This is the preferred way to do conditional grouping, especially if you cannot get them from the source system.
- Open the Query Editor.
- Select the Query you wish to edit.
- Select Conditional Column from the Add Column menu bar, which brings up the entry tab.
- Fill out the rule that matches your conditions.
- Add Rules until all your conditions are met.
- Don’t forget to put in a value for Otherwise to capture any values that do not fall under your conditions.
You then have a grouping column that you can use in your formulas as pictured above.
Wrap-up
Sometimes when you make a mistake, you can get some interesting results. Trying to get a formula to work and deciding to break it up showed me that it was a grouping task and I could solve that in the Query Editor, making the final calculation far easier. However, also learned something new that I can probably use somewhere else.
The source files and sample book are available on GITHUB.