Missing dimensions

Missing dimension measures are commonly used in churn and exception reporting and analyses. For example, a report may be needed which displays the specific products that haven't sold or the past customers who haven't made a purchase in a given filter context. Additionally, missing dimension measures give greater meaning to other dimension measures. For instance, the count of products sold in a period may not be as useful without knowing how many products were not sold over this same period. 

The following DAX measure counts the number of unique customers without Internet Sales history:

Internet Sales Customers Missing = 
CALCULATE(DISTINCTCOUNT('Customer'[Customer Alternate Key]),
FILTER(VALUES('Customer'[Customer Alternate Key]),
ISEMPTY(RELATEDTABLE('Internet Sales'))))

Internet Sales Products Missing =
CALCULATE(DISTINCTCOUNT('Product'[Product Alternate Key]),
FILTER(VALUES('Product'[Product Alternate Key]),
ISEMPTY(RELATEDTABLE('Internet Sales'))))

The Internet Sales Customers Missing measure references the 'Internet Sales' fact table like the customer count measure does, but only within the ISEMPTY() function. The ISEMPTY() function operates as the filter parameter of the FILTER() function and returns a true or a false value for each distinct Customer Alternate Key provided by the VALUES() function. Only the customer rows without any related rows in the Internet Sales fact table are marked as true and this filtered set of customer rows is passed to the DISTINCTCOUNT() function. The same methodology is applied to the Internet Sales Products Missing measure.

The following matrix visual of a Power BI report has been filtered to four calendar quarters and broken out by the Sales Territory Group:

Internet Sales Customers and Customers Missing

Any other dimension table with a relationship to the Internet Sales fact table, such as Promotion and Product could also be used to filter the metrics. 

In this dataset, the customer dimension has 18,484 unique customers as defined by the Customer Alternate Key. Therefore, the sum of the customer count and customers missing measures is always equal to 18,484. As explained in the Filter context section, the subtotal values execute in their own filter context. For example, only 9,024 did not make an online purchase in any of the four quarters, while over 15,000 customers did not make a purchase in each of the four quarters.

Once core dimension metrics have been established such as in the previous examples, additional metrics can be developed which leverage their logic. The following measures identify the count of first year internet sales customers and the count of accessories products which have not sold online, respectively:

Internet Sales First Year Customer Count = 
CALCULATE([Internet Sales Customer Count],'Customer'[Customer History Segment] = "First Year Customer")

Internet Sales Products Missing (Accessories) =
CALCULATE([Internet Sales Products Missing],'Product'[Product Category] = "Accessories")

Dimension metrics, just like the base measures described earlier, may be used in reporting by themselves or may be referenced by other measures. This branching of measures underlines the importance of clearly defining, documenting, and testing the foundational measures of a dataset.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.219.63.95