Using PowerBI Report Builder and given a table of purchases, how can I create a list of unique customers when filtering the table by date?

Question

My data source is in Power BI. I have two tables. One is a list of customers and their data and the other is a list of transactions. The tables are related with a one to many relationship based on a customer ID field. I am wanting to produce a report in Power BI Report Builder that lets me select a start and end date and have it return a list of unique customers. If I didn't need to create a paginated report, I could do all of this in Power BI. My issue is that I need to create a report in Power BI Report Builder so that users can enter their own date ranges.

I have been able to create many reports by creating a new merged table in Power BI and using that as my data source in Power BI Report Builder, but that will not work in this situation. I need to be able to get a dataset in Power BI Report Builder that reports a unique customers who have had one or more transactions between user entered date parameters.

I added created a simple dataset from my Power BI data source, selecting CustomerID from the Customer table and TransactionDate from the Transactions table. I then created two parameters, StartDate and EndDate. I added filters to my dataset to ensure that the TransactionDate is less than or equal to EndDate and greater than or equal to StartDate. I create a table and added CustomerID as the only field.

When I ran the report, I entered a single date for both the StartDate and EndDate parameters. I should have received just a single CustomerID. Instead it not only returned duplicate CustomerIDs, but it returned CustomerIDs that didn't have any transactions in that period.

I'm wondering if there is a way to create a DAX query that will handle this and help me correctly report this information.

Answer 1

In your dataset you need to write a query to power bi similar to this:

EVALUATE SUMMARIZECOLUMNS(
  Table[CustomerID],
  FILTER(TransactionTable, TransactionTable[Date] >= @minDate),
  FILTER(TransactionTable, TransactionTable[Date] <= @maxDate),
  "CountTransactions", COUNTROWS(TransactionTable)
)

This will give you a dataset with 2 columns, the CustomerID and the Number of Transactions and it will have unique CustomerIDs.

If you know how to do it in PowerBI, you can obtain the query behind the table, by using the Performance Analyser:

and then:

From then on, it should be easy to adjust the dax query used by powerbi to a version to be used in the report builder, with parameters.