英文:
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?
问题
我的数据源在Power BI中。我有两个表格。一个是客户及其数据的列表，另一个是交易列表。这两个表格基于客户ID字段建立了一对多的关系。我想在Power BI Report Builder中生成一个报告，让我选择开始日期和结束日期，并返回一个唯一客户的列表。如果我不需要创建分页报告，我可以在Power BI中完成所有这些。我的问题是，我需要在Power BI Report Builder中创建一个报告，以便用户可以输入自己的日期范围。
我已经能够通过在Power BI中创建一个新的合并表格，并将其用作Power BI Report Builder中的数据源来创建许多报告，但在这种情况下将无法使用这种方法。我需要能够在Power BI Report Builder中获取一个数据集，报告在用户输入的日期参数之间有一笔或多笔交易的唯一客户。
我从我的Power BI数据源创建了一个简单的数据集，选择了Customer表中的CustomerID和Transactions表中的TransactionDate。然后我创建了两个参数，StartDate和EndDate。我为我的数据集添加了过滤器，以确保TransactionDate小于或等于EndDate，并大于或等于StartDate。我创建了一个表格，并将CustomerID作为唯一字段添加。
当我运行报告时，我为StartDate和EndDate参数输入了相同的日期。我应该只收到一个CustomerID。但它不仅返回了重复的CustomerID，还返回了在该期间没有任何交易的CustomerID。
我想知道是否有一种方法可以创建一个DAX查询来处理这个问题，并帮助我正确报告这些信息。
英文:
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.
答案1
得分: 0
在你的数据集中，你需要编写一个类似于以下的 Power BI 查询：
EVALUATE SUMMARIZECOLUMNS(
Table[CustomerID],
FILTER(TransactionTable, TransactionTable[Date] >= @minDate),
FILTER(TransactionTable, TransactionTable[Date] <= @maxDate),
"CountTransactions", COUNTROWS(TransactionTable)
)
这将给你一个包含两列的数据集，CustomerID 和交易数量（Number of Transactions），并且它将具有唯一的 CustomerID。
如果你知道如何在 Power BI 中执行这个操作，你可以通过使用性能分析工具获取表格背后的查询：
然后：
从那以后，将 Power BI 使用的 DAX 查询调整为可用于报告生成器（report builder）的版本应该很容易。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论