Logical functions in Power BI
1. Logical functions in Power BI
Hello! Let's have a deeper look at logical functions. Previously we calculated the actual transaction amount and the corresponding ranking. We see the total of the actual transaction amount is the sum of all the amounts, which is what we want, but the total of the rank function is displayed as one, which is not what we want, we would prefer this to be blank. We could hide the total row in general, but that means the total transaction amount would disappear as well. We'll use an IF statement in combination with the HASONEVALUE function to solve this problem. Logical functions really shine when you start combining it with other handy functions, but let's build a function with the HASONEVALUE statement first to see how it works. This function returns TRUE when the context of the column name returns exactly one value. Otherwise it returns FALSE. We name the function Has One Value for now, and wrap ProductCategoryName inside and add it to the table. It returns TRUE for Audio and the other product categories, because the function returns exactly one value, but it's false for the Total, because the total contains eight values. Let's combine this boolean expression with an IF statement. We navigate to the actual transaction rank. If the HASONEVALUE statement is TRUE, we want to rank it. But if it's not we want to return a blank, for which we can use a blank expression. We wrap the RANKX() statement inside an IF function, and use the same HASONEVALUE argument as the logical test. If true we want to display the rank, and if False we want to display a blank. Now we can see the rank is not visible in the Total line. Finally we can delete the HASONEVALUE metric we created, as it was purely created to help understand what's going on. For the second part of this demo we'll move on to bucketing. We want to create separate buckets, depending on the Actual Transaction Amount. It might not be that useful in this case because we only have 8 categories, but this is a very common business request which adds a lot of value when you have a lot of different categories. Let's name the calculation transaction buckets and do so using a SWITCH statement If the actual transaction amount is below 250 million, the text will display under 250 million. If the transaction amount is below 1 billion, it will display 250 million - 1 billion and so forth. Note the SWITCH statement evaluates the arguments in order, So only if the first expression is FALSE, the second expression will be evaluated, and so on. Because of that reason we started with the lowest amount. Let's press enter and add the buckets to the table. When we throw it into the table we clearly see the groups of each transaction. That was quite easy! The switch statement is very powerful and often preferred over nested IF functions. That's it for this demo. Over to you to start using logical statements in the exercises!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.