Power Query Problems

How to Find Unsubscribed Customers Using Power Query and DAX in Excel

Ayan Masood Oct 15, 2026 869 views

Question details

The user wants to identify families or customers who are not subscribed to any premium product using Excel Power Query and DAX, but is struggling with incorrect calculations based on monthly spending data.

How to Find Unsubscribed Customers Using Power Query and DAX in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing customer data across product and fact tables to determine premium subscription status without relying on transaction volume.
Observed behavior: The current logic incorrectly classifies customers as unsubscribed if they have no transactions in a given month, rather than checking their actual subscription status.

Before you start

Ensure you have access to your raw Customer, Product, and Fact tables within Excel, and verify that your Data Model relationships are properly configured.

Solution 1Recommended

Define Subscription Logic Using DAX and Table Relationships

Use DAX to evaluate subscription status directly from the product table relationships rather than relying on monthly spending data.

Basing a customer's subscription status solely on their transaction history in a given month often leads to false negatives. Customers may retain an active premium subscription without making additional monthly purchases. The correct approach is to evaluate the relationship between the customer table and the product table directly.

Relate the Data Tables

Open the Power Pivot window and ensure your Customer, Product, and Fact tables are properly linked using unique identifiers such as CustomerID and ProductID.

Create a DAX Measure for Premium Status

Write a DAX measure that filters the Customer table based on the absence of related records in the Fact table that link to a Premium Product. Utilize functions like CALCULATE and ISEMPTY.

Filter Unsubscribed Families

Apply the new measure in your PivotTable to flag or filter out families where the count of premium subscriptions equals zero, regardless of their monthly transaction volume.

Define Subscription Logic Using DAX and Table Relationships

Data Access Check: If you are working with a sample workbook that fails to download, try accessing the link from a different network connection or request a direct file transfer.

Free Microsoft Office alternative

Streamline Your Data Analysis with WPS Office

While Microsoft Excel offers Power Query and DAX, complex data modeling can sometimes lead to logic errors and steep learning curves. WPS Office provides a lightweight, free alternative with robust data processing tools, PivotTables, and full compatibility with Excel formats, making everyday data analysis simpler.

1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your device.
2. Open Your Data File: Launch WPS Spreadsheet and open your existing .xlsx file containing the customer and product data.
3. Analyze with PivotTables: Go to the Insert tab, select PivotTable, and easily cross-reference your customers against their subscribed products without writing complex code.

Highly compatible with Microsoft Excel (.xlsx, .xls) files.Intuitive user interface that requires no steep learning curve.Free and lightweight alternative to heavy data modeling tools.Built-in advanced PivotTable features for quick customer data summaries.

microsoft office alternative - wps office

Frequently Asked Questions

Why does counting monthly transactions misclassify my unsubscribed customers?

Customers might hold an active subscription but make zero new transactions in a specific month. Counting monthly sales facts will incorrectly flag them as unsubscribed during their inactive months.

How do I properly relate Customer and Product tables in Excel?

You can use the Power Pivot Data Model feature to draw a relationship line between the CustomerID in your Customer table and the CustomerID in your Fact table, and similarly connect the ProductID to the Product table.

Which DAX function is best for finding missing subscription records?

Functions like ISEMPTY or using CALCULATE with an ISBLANK filter against the related fact table are highly effective for identifying customers without specific premium products.

What should I do if the sample Excel data file won't download?

Try switching your network connection, disabling any active VPNs, or reaching out to the original source to request the sample data file directly via email.

Excel & Data Problems