Consider a scenario where the Product Id is present in the Sales table but is missing from the lookup table. This simply means that you sold a product (in the Sales Transaction Data) that was missing the Lookup Table (Products Data).
In this blog post, I’ll talk about 2 ways to quickly find missing values in the lookup tables that appear in the transactions table.
Find Missing Values from Lookup Tables Video
Consider this Pivot Table
We have Year, Product Code and Units presented, notice the Units for blank Product Codes. This means that we sold a few products that were missing from the Products Master (Lookup Table).
Let’s find the missing Products IDs.
#1 Find Missing Values – Using the RELATED function
One non preferred way, is to create a Column in the Sales Table and write a VLOOKUP i.e. RELATED Function to see which products appear in the sales table that are missing from the Products Table.
Creating a column, just to find the missing products is an inefficient way to solve this problem, let’s leverage the relationship between the Sales and Products table.
#2 Find Missing Values – Using Relationships
Here is a simple 2 step approach. Create a Matrix / Table Visual.
- From the Field List, drag the Product ID from the Sales Table.
- Drag the Product Code from the Products Table against the Product ID in the rows.
The blanks rows in the second column (from the products table) are your missing values / Product IDs.
Summary
Using relationships is super helpful to find inconsistencies in the data, especially when values are missing from the Lookup Table. Although creating a column is an option but I’d personally recommend a faster approach (using relationships) to solve this problem.
More on Data Modeling and DAX
- Switch between Current Period and YTD Calculation
- Allocation Calculations in Power BI
- Calculate Percentile in Power BI
- Running Total for Dates and Non Dates in Power BI
- Slab / Tiered Calculations in Power BI
