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?

Related: https://stackoverflow.com/questions/52525377 – Alexis Olson May 14 '20 at 14:03 — Alexis Olson, May 14 '20 at 14:03

score 2 · Accepted Answer · answered May 14 '20 at 14:10

2

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

answered May 14 '20 at 14:10

Alexis Olson

38,724
7
42
64

I have the same type of problem but when I implement your answer i get: A table of multiple values was supplied where a single value was expected. – Federico Gentile Jun 12 '21 at 22:24
1

You may have a tie resulting in multiple rows. Try using MAXX instead of SELECTCOLUMNS – Alexis Olson Jun 12 '21 at 22:28
Thanks for the hint! Yes i think that was the problem. I used the following line of code MAXX(TOPN ( 1, ProductKey, [ProdCount] ), 'FactInternetSales'[ProductKey]) – Federico Gentile Jun 12 '21 at 22:38

DAX - add column with most common value

1 Answers1

Linked