cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JG_0117
New Member

Returning Most Recurring Field per each Unique Value in Separate Column

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. 

 

PBIExample1.pngPBIExample2.png

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.

JG_0117_5-1610392074304.png

 

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!

 

2 ACCEPTED SOLUTIONS
PaulDBrown
Super User II
Super User II

@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:

Result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

v-alq-msft
Community Support
Community Support

Hi, @JG_0117 

 

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

Table:

b1.png

 

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:

b2.png

 

Best Regards

Allan

 

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

View solution in original post

3 REPLIES 3
v-alq-msft
Community Support
Community Support

Hi, @JG_0117 

 

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

Table:

b1.png

 

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:

b2.png

 

Best Regards

Allan

 

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

View solution in original post

PaulDBrown
Super User II
Super User II

@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:

Result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

parry2k
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.