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

Background Example

Background Example

Background Example

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

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]))

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

Polishing

  • Filter for product availability

Filter Product Availability

Filter 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

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

Filter Product Availability

Filter Product Availability

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

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

Filter Product Availability

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

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]), 
"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

Customers and Orders Measures

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.

End