Power BI and Missing Values

Tyler Bradley

Setup

Background Example

Basic Strategies

Better Strategy

Polishing

Background Example

Start out with some data to graph

Background Example

month = STARTOFMONTH(orders_small[date])

Background Example

sum_amount = SUM(orders_small[dollar_amount])

distinct_months = DISTINCTCOUNT(orders_small[month])

monthly_average = DIVIDE([sum_amount], [distinct_months])

End Product

Line graphs and tables by product over time

Dollar amount
Number of distinct customers
Number of orders

Filter by product and month

Fill in zeros when product was available

Basic Strategies

Show missing items

Doesn’t work in this example

Basic Strategies

sum_amount_zero = SUM(orders_small[dollar_amount]) + 0

Doesn’t fix table

Basic Strategies

monthly_average = DIVIDE([sum_amount], 12)

Doesn’t fix graph

Basic Strategies

Clean up the data by:

Importing monthly data

Importing zero

Basic Strategies

Tend to work easily

But tend to not be extendable

Better Strategy

Setup with Bigger Dataset

End Product

Line graphs and tables by product over time

Dollar amount
Number of distinct customers
Number of orders

Filter by product and month

Fill in zeros when product was available

Better Strategy

Calendar Table Setup

Calendar Table

Table created in Power BI that contains all the dates you need and associated information, using the CALENDAR function.

The CALENDAR function needs a start date and end date.

Fills in the day in between (including start and end dates).

Three examples

Calendar Table

cal_table = CALENDAR(
  DATE(2020, 01, 01),
  DATE(2020, 12, 31))

cal_table = CALENDAR(
  DATE(YEAR(TODAY()) - 1, 01, 01),
  DATE(YEAR(TODAY()) - 1, 12, 31))

cal_table = CALENDAR(
  MIN(orders_big[date]),
  MAX(orders_big[date]))

We have three different ways to create a date table using the CALENDAR function. The first way provides specific dates. This is useful if you need the graph to keep the same date range even if your data changes. The second way uses the TODAY function to get the current date. This will keep your dashboard current no matter what happens with your data. The last way pulls the start and end dates from the data. This will keep your graphs matching the data even if it doesn’t refresh. I tend to use either of the last two depending on the business need.

There are other options like CALENDARAUTO or setting a date table, but we’ll stick with CALENDAR for this presentation. It’s definitely worth your time to spend an afternoon looking into the other options if you’re going to implement this at your work.

Calendar Table

cal_month = STARTOFMONTH(cal_table[DATE])

Calendar Table

Better Strategy

Calendar Table Setup +

Star Schema

Star Schema

Main orders_big table in the middle

Important dimensions on the outside

Star Schema

product = SUMMARIZECOLUMNS(orders_big[product])

customer = SUMMARIZECOLUMNS(orders_big[customer_id])

Better Strategy

Calendar Table Setup +

Star Schema +

ADDMISSINGITEMS()

ADDMISSINGITEMS()

Adds rows with empty values to a table returned by SUMMARIZECOLUMNS. (MS Docs)

Add the rows with empty measure values back. (dax.guide)

ADDMISSINGITEMS()

ShowAll_ColumnName - What you want to fill in

Table - SUMMARIZECOLUMNS table with missing data (based on orders_big table here)

GroupBy_ColumnName - What you want to fill in (same as ShowAll_ColumnName unless ROLLUP)

FilterTable - Filter out unneeded combinations

ADDMISSINGITEMS()

ShowAll_ColumnName -

cal_table[cal_month], 'product'[product]

Table -

SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
  "amount", SUM(orders_big[dollar_amount])

ADDMISSINGITEMS()

orders_zeros_added = 
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
  "amount", SUM(orders_big[dollar_amount])), 
cal_table[cal_month], 'product'[product])

ADDMISSINGITEMS()

orders_zeros_added = 
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
  "amount", SUM(orders_big[dollar_amount])), 
cal_table[cal_month], 'product'[product]),
"amount filled", IF(ISBLANK([amount]), 0, [amount])))

ADDMISSINGITEMS()

High Density Sampling

Might need to turn this off

ADDMISSINGITEMS()

Polishing

Filter for product availability

Filter Product Availability

product_availability = 
FILTER(CROSSJOIN(cal_table, product), 
  cal_month[month] >= product_dates[start_month] && 
    cal_month[month] <= product_dates[end_month])

Filter Product Availability

No filter

orders_zeros_added = 
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
"amount", SUM(orders_big[dollar_amount])), 
cal_table[cal_month], 'product'[product])

Filter

orders_zeros_added = 
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
"amount", SUM(orders_big[dollar_amount])), 
cal_table[cal_month], 'product'[product], product_availability)

Filter Product Availability

orders_zeros_added = CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
"amount", SUM(orders_big[dollar_amount])), 
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount])))

Filter Product Availability

What if you want the graph to be continuous or want all the months with missing for unavailable?

Let’s backtrack a little bit…

Filter Product Availability

product_zeros = SUMMARIZECOLUMNS(orders_big[product])

cal_table_month = 
var CalendarDates = 
    GENERATE( CALENDAR(DATE(2020, 01, 01), 
                       DATE(2020, 12, 31)),
    ROW("month", DATE(YEAR( [Date] ), MONTH( [Date] ), 01)))
return
SUMMARIZE(CalendarDates, [month])

product_availability = FILTER(
CROSSJOIN(cal_table_month, product_dates), 
  cal_table_month[month] >= product_dates[start_month] && 
    cal_table_month[month] <= product_dates[end_month])

Filter Product Availability

available = LOOKUPVALUE(product_availability[product], 
product_availability[month], orders_zeros_added[cal_month], 
product_availability[product], orders_zeros_added[product])

amount_filled_missing = IF(ISBLANK(orders_zeros_added[available]), 
  BLANK(), orders_zeros_added[amount filled])

Filter Product Availability

BLANK is not like SQL NULL

Converts to 0 in sums and subtractions
Stays BLANK in divisions and multiplication

1 - (A/B) = (B/B) - (A/B) = (B - A)/B

If B is BLANK
Left side = 1
Right side = BLANK

Polishing

Filter for product availability

Add Customers and Orders measures

Customers and Orders Measures

orders_zeros_added = 
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
"amount", SUM(orders_big[dollar_amount]), 
"customers", DISTINCTCOUNT(orders_big[customer_id])), 
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount]), 
"customers filled", IF(ISBLANK([customers]), 0, [customers])))

Customers and Orders Measures

orders_zeros_added = 
CALCULATETABLE(ADDCOLUMNS(
ADDMISSINGITEMS(cal_table[cal_month], 'product'[product], 
SUMMARIZECOLUMNS(cal_table[cal_month], 'product'[product], 
"amount", SUM(orders_big[dollar_amount]), 
"customers", DISTINCTCOUNT(orders_big[customer_id]), 
"orders", DISTINCTCOUNT(orders_big[order_number])), 
cal_table[cal_month], 'product'[product], product_availability),
"amount filled", IF(ISBLANK([amount]), 0, [amount]), 
"customers filled", IF(ISBLANK([customers]), 0, [customers]), 
"orders filled", IF(ISBLANK([orders]), 0, [orders])))

Customers and Orders Measures

Might need to watch DISTINCTCOUNT if needed in other aggregations.

Bring all customers through in the SUMMARIZECOLUMNS, but not in the ShowAll_ColumnName options.

This will fill in missing product-month pairs but not in combination with customers/order.
Get rows with no customer_id or order_id.

Polishing

Filter for product availability

Add Customers and Orders measures

Filters

Filters

Just use the built in filters for the table

Use Star Schema again (will need to create new tables)

That’s pretty much it.