CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Andrej Hafner
Andrej Hafner

Reputation: 99

DAX - add column with most common value

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

Example:

enter image description here

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:

ry

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

Upvotes: 1

Views: 3507

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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

Upvotes: 2

Related Questions