DAX - add column with most common value

Question

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

Example:

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:

Company A, most common value of "Document Category" (where link is not empty) is "Car"
Company B has "Airplane"
Company C has "Other"

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

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-14T14:10:00

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 - add column with most common value

1 Answers