When using Power BI DAX, often times we encounter a BLANK value, and therefore the result/visual shows “(blank)”, which we don’t like to see. In this case, COALESCE comes in handy with readablility and performance to enhance our DAX function. Syntax COALESCE( <Value 1>, <Value 2>, <Value 3>) COALESCE returns the…

Sometimes we would struggle about whether to use ALL or ALLSELECTED. In this article, I will introduce some tips and scenarios to help decide when to use. ALLSELECTED() Syntax ALLSELECTED(<tableName> or <columnName>) ALLSELECTED removes/ignores all filters but takes into account the overarching context in the report page. For example, most of the…

Sometimes it’s confusing for us to decide when to use HASONEVALUE() or HASONEFILTER(). Here we will talk about the difference between the two functions and some tips about when to use. Summary HASONEVALUE() works based on cross-filters, while HASONEFILTER() works by a direct filter. HASONEFILTER Syntax HASONEFILTER(<ColumnName>) Return TRUE if the number of…

We always want to make our physical relationships from the columns of unique value, or the highest granularity level. In other words, we want to have relationships between table based on the highest granularity level such as DATE. (If DATE is the most granular level.) However, sometimes our data is…

In the big data era, 99.9% of data is recorded based on dates. We need dates to look into the improvements of your business compared with yesterday, last month, last year, etc. Therefore, in this article, we will discuss how the importance of creating a DYNAMIC calendar table is and…

Assuming we would like to make our Power BI report more interactive by allowing users to choose and show any metrics they need in a visual. That is, we can change measures/columns based on slicer. We will discuss how to do this in the article. Step 1. Have the Measures…

Are you getting tired of writing “if” functions? More specific, are you getting tired of writing a lot of “if” functions with different criteria? If yes, here comes an easy-to-understand and easy-to-debug method: SWITCH. SWITCH = Nested If Statements Syntax SWITCH(expression, value1, result1, value2, result2, … … else ) expression is any DAX expression that returns…

EARLIER is not an easy-to-understand DAX function for Power BI users. I will use some examples to show you how to use EARLIER function. What is EARLIER? Syntax: EARLIER(<column>, (number)) The purpose of the function is to create a reference to a column value on an outer loop of the evaluation of the…

Most of the time we would like to know the running(cumulative) total of your sales. Here I will show you how to do that in three different scenarios. Case Let’s say you have five products (A-E) and the sold quantity (Sold QTY) of each product by date. …