3 Awesome DAX Tricks to deal with BLANKS

It's frustrating when unwanted BLANK values keep mushrooming in various places in my Power BI Visuals. Today I am going to share with you 3 DAX Tricks to deal with such BLANK values.

 

3 DAX Tips – Video

 

#1 Remove LY Sales when Current Year Sales is not present 

In my pivot table below I have Total Sales and LY Sales but I’d like to remove LY Sales numbers where Total Sales is not present.

LY Sales =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR('Calendar'[Date])
)

To do this I can modify LY Sales by adding a simple if condition

LY Sales =
IF(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR('Calendar'[Date])
    )
)

Mentioning [Total Sales] as first argument in IF Condition, implies [Total Sales] <> BLANK(). Here’s the output.

 

#2 How to do Strict Equals

See this pivot table.

Here, the Dummy Cost measure should follow this logic ➜ If Total Sales = 0 then = 100 else Dummy Cost = 50% of Total Sales.

This calls for a simple measure like this.

Dummy Cost =
    IF(
        [Total Sales] = 0, 100,
        [Total Sales] * 0.5
    )

1. The problem, however, is that Dummy Cost shows 100 even if Total Sales was Blank, NOT Zero but BLANK.
2. Power BI treated that BLANK as zero and assigned it a value of 100. Not cool👎

To alter this behaviour of Power BI, all we have to do is use double equal to '==' in case of single equal to '=' in the if condition. Also knows as “Strict Equals”, here's the DAX and the result.

Dummy Cost =
    IF(
        [Total Sales] == 0, 100,
        [Total Sales] * 0.5
    )

 

#3 Get rid of Blanks using CALCULATE

I have a Pivot table with Year, Month and Commission Paid. All looks okay, except that there is a blank entry at the start of the table. It looks awful, let’s kill it.

But first let’s understand why it got here in the first place. The DAX for Commission Paid looks like this:

Commission Paid =
CALCULATE(
    [Total Commission],
    USERELATIONSHIP('Calendar'[Date], Sales[Aff Payout Date])
)

Here is a snapshot of the Affiliate Payout Date.

So, why a Blank appeared in the Pivot Table?

1. While evaluating this is DAX Power BI evaluates the inactive relationship between Affiliate Payout Date and Calendar Date and it finds that there are a bunch of BLANKS in Affiliate Payout Date column against which there is no commission.
2. It combines all these blank values into a single row and assigns a zero to the Commission Paid.

To solve this, we are going to make a slight modification to our DAX.

Commission Paid =
CALCULATE(
    [Total Commission],
    USERELATIONSHIP('Calendar'[Date], Sales[Aff Payout Date]),
    Sales[Aff Payout Date]
)

1. Adding Sales[Aff Payout Date] (Column) as filter to the CALCULATE function implies that Sales[Aff Payout Date] <> BLANK().
2. It therefore evaluates the measure for only non blank rows.

Sweet, we killed it 🔪

 

 

More on DAX

1. USERRELATIONSHIP in DAX
2. 3 Complex DAX Examples
3. Sum of Max Values in Power BI
4. Display Table or Matrix Until the Selected Date
5. Understanding the DAX Filter Function