You have to ask yourself, “Are you looking for the count of records or the count of unique values?” You can answer this by whether one value will be considered more than once. For example, if you ask what the total number of sales is and one sale has multiple items within it, then that sale would show up as multiple rows, so you are looking for the count of unique values. If it is not a situation where duplicates can happen, then you need to look at how many rows match your criteria. Power BI IF Statement for temporary measures is a great way to get around this, but it should be the first thing you try before you move to the next step.
What is Power BI IF Statement For?
- This is the way to go if you are looking for duplicate records. There are better ways to find duplicates, but it gets you close enough. If two records have the same values, they will both show up, and you can look at how many total.
- If you are looking for unique values and don’t care about duplicates, this is your next best solution because it does not try to count duplicates but only matches rows with the same values.
- If you have a small collection of samples you want to test to determine whether data from multiple sources is the same or different, this is your last option because it has little or no unique value (it will match duplicates). That is because it relies on all values in both columns to be unique, so it may need more data to have enough information and give you data.
All three options, if combined, provide a better match than if they were compared one at a time individually.
The Power BI IF Statement For
Now that you know what is going on and why to use the IF, you are ready to create your own IF formula. Remember that it needs to be unique in both columns AND values. It is not good enough to have an IF statement in one column and then copy and paste it into another. By the way, that would be bad because you would create redundant data.
For example, let’s say you have an Orders table and a Products table. You only have 5 records in each table, but you have one table in US Dollars, and the other is in Euros. When you first look at this, you do not think they are in the same order because they are different currencies, but once you switch the currency to Euros, they match up.
In the table for Orders, you have two columns. SalesOrderID and ProductID. In the Products table, you have two columns, OrderDate and ProductID. Once you switch over to Euro, this time, you realize that they are the same table because they are the same values in both columns.
You can use the Power BI IF Statement for this. In this situation, you need to use the IF statement for temporary measures because it will show you rows for when it does not match. The IF statement does not try and count duplicates but only matches rows with the same values. If your goal is only to find unique fields, then this is the way.
Times Comparison in Power BI
It is important to know whether you want to compare times or the value they are comparing against. You could create two columns in your table and use the IF statement with a “test” value. For example, if you have a table of Sales amounts, then you can use something like IF (Gross Profit – Fixed Cost) * (number of days worked) > 100 and then view it in the Graph tab for time. That can confuse new BI users because if you compare Days Worked, how would you know what the Gross Profit is in days?
For some comparisons, such as value and time, the time should always be on the left side of the equal sign. For example, if you are looking for unique values or values that occur more than once (therefore having duplicates in this situation), then use IF (SalesOrderID = 2). That will tell you that SalesOrderID is unique. You can compare against other columns, but then you must compare values, and the fields need to be on the same side as the equal sign. For example, IF (ProductDescriptionID = 1) would compare ProductDescriptionID to one unique value.
The Power BI IF Statement for Quick Measures
That is the formula you created to create your IF statement. You can work with your data in Power BI Desktop to fine-tune it, but this is the “off the shelf” version you can use immediately.
If you are looking for unique values and not concerned about duplicates, this is the formula you want to use because it tries to do what you want, even if the data may be a partially unique value. For example, if you are looking for unique customers and you only have their first name and last name, then you would use the formula IF (FirstName = “Bob”) AND (LastName = “Smith”). You could also simplify this by using an array formula to create a single column with names. In this situation, this is your best option.
If you are looking for unique data and not concerned about duplicates, then you want to use the Power BI IF statement for temporary measures. For example, let’s say we have a table of Sales Amounts. In this situation, because we are only using two columns, this formula can be used to determine duplicates. The formula is =IF (SalesAmount > [$”Test Value”], SalesAmount, “*SKIPPED*”)
The first part of the formula (Sales Amount > Test Value) tells you that it is true if the value is greater than what you specify. In this situation, we are using $”Test Value” which are the three dollar signs followed by a space and then whatever you want to test it against, so it needs to be exactly what you want to use in that column. It can also be empty, meaning you want it to return all zero values.
The part of the formula (Sales Amount > $”Test Value”) says it will only return true if its value is greater than what you specified. In this case, we are testing against empty test values, meaning that the formula will not return anything if it evaluates as true. It needs to be filled with the value you want to test against for it to return data from that statement.
The second half of the formula (Sales Amount > $”Test Value”) is the part that tells you what happens if it is true. In this situation, we are using Sales Amount, which means that it will return that same value and skip the “*SKIPPED*”, which tells us that we did not match anything in that column. When you create a formula like this, it creates several different results depending on whether there is any data in that column.
In this case, we are using the IF Statement for Quick Measures, so it will not return anything if there is no data.
Conclusion:
Enterprise DNA tells you about the Power BI IF Statement for Quick Measures. If you want to learn more about the Power BI IF statement for Quick Measures and how it can help you, check out our blog.
