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.

理想情况下，我不会硬编码值，就像我以后希望将 Product列用作参数一样。如果除了在DAX中进行翻译之外，还有其他建议，也将不胜感激！ （即使用PowerBI中的过滤器或其他工具的IE）

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 函数从行中获取值并将其传递给filter语句。

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])
        )
    )

我为您的问题建议的是通过简单地将其创建为TRUE / FALSE标志检查运行计数是否为1。此公式将得出一个布尔值标志。

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屋！