PowerBI DAX - 根据多个条件识别第一个实例 [英] PowerBI DAX - Identifying first instance based on multiple criteria

问题描述

我正面临着试图在数据库中识别某人(由 ID 列识别)首次购买产品的第一个实例.该人可能在不同的日子多次购买该产品，或者在同一天购买不同的产品.我编写了一个 excel 公式，可以让我到达那里，但在转换为 DAX 时遇到了麻烦.

I'm faced with trying to identify the first instance in a database where someone (identified by the ID column) has purchased a product for the first time. It's possible for said person to purchase the product multiple times on different days, or purchase different products on the same day. I drummed up an excel formula that gets me there, but am having trouble translating into DAX.

=COUNTIFS(ID,ID,PurchaseDate,"<="&PurchaseDate,Product,Product)

在一审"中哪个值正确?列.

Which results in the correct values in the "First Instance?" Column.

理想情况下，我不必对值进行硬编码，因为我希望将来使用产品"列作为参数.如果除了在 DAX 中翻译之外还有其他建议，那也将不胜感激！(IE 使用过滤器，或 PowerBI 中的其他工具)

Ideally I won't have to hardcode values, as I would like to use the "Product" column as a parameter in the future. If there are other suggests aside from translating this in DAX, that would also be appreciated! (IE using filters, or other tools in PowerBI)

提前致谢！

推荐答案

这与我对另一个问题的回答非常相似(您可以找到 这里).

This is very similar to an answer I gave to another question (which you can find here).

在那个问题中，请求是查看给定行条件(产品、年份等)的行数.我们可以稍微修改一下，让它在您的问题中发挥作用.

In that question, the request was to see a running count of rows for the given row's criteria (product, year, etc.). We can modify that slightly to get it to work in your problem.

这是我在上面链接的答案中提供的公式.基本概念是使用 EARLIER 函数从行中获取值并将其传递给过滤器语句.

This is the formula I provided in the answer I linked above. The basic concept is to use the EARLIER functions to get the value from the row and pass it into the filter statement.

Running Count = 
    COUNTROWS(
        FILTER(
            'Data',
            [ProductName] = EARLIER([ProductName]) &&
            [Customer] = EARLIER([Customer]) &&
            [Seller] = EARLIER([Seller]) &&
            [Year] <= EARLIER([Year])
        )
    )

我对您的问题的建议是通过简单地检查运行计数是否为 1 将其创建为 TRUE/FALSE 标志.此公式将评估为布尔标志.

What I would suggest for your problem is to create this as a TRUE/FALSE flag by simply checking if the running count is 1. This formula will evaluate to a Boolean flag.

First Instance = 
    COUNTROWS(
        FILTER(
            'Data',
            [ID] = EARLIER([ID]) &&
            [Product] = EARLIER([Product]) &&
            [Purchase Date] <= EARLIER([Purchase Date])
        )
    ) = 1

这篇关于PowerBI DAX - 根据多个条件识别第一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！