DAX - 添加具有最常见值的列 [英] DAX - add column with most common value

问题描述

在 Power BI 中，我有包含以下数据的表格，列公司"、文档"、链接"、价格"、文档类别"

In Power BI, I have table with following data, columns "Company", "Document", "Link", "Price", "Document Category"

示例:

我需要添加新列(例如公司类别)，其中文档类别"的最准确值取决于公司"，并且根据规则，链接"列值不为空.

I need to add new column (for example Company Category), where will be most accured value of "Document Category" depend on "Company" and with rule, that "Link" column value is not empty.

所以新表看起来像这样:

So the new table would look like this:

• A 公司，文档类别"的最常见值(其中链接是不为空)是汽车"

• Company A, most common value of "Document Category" (where link is not empty) is "Car"

B 公司有飞机"

C 公司有其他"

是否可以通过 DAX 在 Power BI 中为此案例添加新列?

It's possible to add new column for this case in Power BI via DAX?

推荐答案

这类似于 我在评论中链接的问题.对于每个Company，您要计算每个Document Category 出现的次数，然后选择最上面的.

This is similar to the question I linked in the comments. For each Company, you want to count how many times each Document Category appears and then pick the top one.

Company Category =
VAR CurrRowCompany = Table1[Company]
VAR DocumentCategories =
    SUMMARIZE (
        FILTER ( Table1, Table1[Company] = CurrRowCompany ),
        Table1[Document Category],
        "DocCount", COUNT ( Table1[Document Category] )
    )
RETURN
    SELECTCOLUMNS (
        TOPN ( 1, DocumentCategories, [DocCount] ),
        "Category", Table1[Document Category]
    )

这篇关于DAX - 添加具有最常见值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！