Returning Most Recurring Field per each Unique Value in Separate Column

[JG_0117]

Hello! I have a dataset that has a project number in one field and the project type in another. There are multiple project types per project number, however I would like to only display the most recurring project type for each specific project number. In the example below, 123456 has Labor occuring twice, Material once, and OH once. I would prefer that only Labor shows up for the project type. 

The below calculated column counts how many times each project type occurs per project #, however I'm not sure how I can utilize that to return the actual project type field instead of a value.

I would prefer to be able to do this in PowerQuery if possible considering how I'll be using the data. If not, how should this be done with calculated columns? 

Thank You!

[PaulDBrown · Accepted solution]

@JG_0117 

Try the following:

1) Number of project type rows per Proj Number

Number Proj. Type = COUNTROWS(FactTable)

2) Highest number of proj type rows per proj. number:

Highest Project Type per Proj Number = 
VAR calc = MAXX(ALLEXCEPT(FactTable, FactTable[Project number]), [Number Proj. Type])
RETURN
IF([Number Proj. Type] = calc, [Number Proj. Type])

And you can then use this last measure in your visual or as a filter in the filter pane:

[v-alq-msft · Accepted solution]

Hi, @JG_0117 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result = 
var tab = 
SUMMARIZE(
    FILTER(
       ALL('Table'),
       [Project Number]=MAX('Table'[Project Number])
    ),
    'Table'[Project Number],
    'Table'[Project Type],
    "Count",
    COUNTROWS('Table')
)
var m = 
MAXX(
    tab,
    [Count]
)
return
CONCATENATEX(
    FILTER(
        tab,
        [Count]=m
    ),
    [Project Type],
    ","
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown · Accepted solution]

@JG_0117 

Try the following:

1) Number of project type rows per Proj Number

Number Proj. Type = COUNTROWS(FactTable)

2) Highest number of proj type rows per proj. number:

Highest Project Type per Proj Number = 
VAR calc = MAXX(ALLEXCEPT(FactTable, FactTable[Project number]), [Number Proj. Type])
RETURN
IF([Number Proj. Type] = calc, [Number Proj. Type])

And you can then use this last measure in your visual or as a filter in the filter pane:

[parry2k]

@JG_0117 what is the logic to pick the labour in your example because it has more rows? What happens if there is a tie? Your logic is not clear.