Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

Grouping by a field and finding the max

Hello,

So I have the following table structure:

And use this DAX formula to find the total amount of Deals by Owner by Phase (one for each phase):

Phase 1 2022 Approvals Count by Owner =
CALCULATE(
DISTINCTCOUNT('Deal Review'[Deals Reviewed (Since 2019)]),
FILTER(
ALLEXCEPT('Deal Review','Deal Review'[Owner]),
SEARCH("2022", 'Deal Review'[Phase 1], , BLANK() ) > 0
))

I would now like to display on cards, the owners name that has the most Phase 1, Phase 2 and Phase 3's.  Not sure how to accomplish this?

Thank you.
1 ACCEPTED SOLUTION
Community Support

Hi @aashton

Thanks for reaching out to us.

Let's take phase 1 as an example, create the measures
-

Phase 1 2022 Approvals Count by Owner =
CALCULATE (
DISTINCTCOUNT ( 'Deal Review'[Deals Reviewed (Since 2019)] ),
FILTER (
ALL ( 'Deal Review' ),
'Deal Review'[Owner] = MIN ( 'Deal Review'[Owner] )
&& SEARCH ( "2022", 'Deal Review'[Phase 1],, BLANK () ) <> BLANK ()
)
)

-

max owner =
VAR _max =
MAXX ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] )
RETURN
CALCULATE (
MAX ( 'Deal Review'[Owner] ),
FILTER ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] = _max )
)

result

Best Regards,

Community Support Team _Tang

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

2 REPLIES 2
Community Support

Hi @aashton

Thanks for reaching out to us.

Let's take phase 1 as an example, create the measures
-

Phase 1 2022 Approvals Count by Owner =
CALCULATE (
DISTINCTCOUNT ( 'Deal Review'[Deals Reviewed (Since 2019)] ),
FILTER (
ALL ( 'Deal Review' ),
'Deal Review'[Owner] = MIN ( 'Deal Review'[Owner] )
&& SEARCH ( "2022", 'Deal Review'[Phase 1],, BLANK () ) <> BLANK ()
)
)

-

max owner =
VAR _max =
MAXX ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] )
RETURN
CALCULATE (
MAX ( 'Deal Review'[Owner] ),
FILTER ( 'Deal Review', [Phase 1 2022 Approvals Count by Owner] = _max )
)

result

Best Regards,

Community Support Team _Tang

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

Helper V

Yes, that worked!!! Thank you so much

Helpful resources

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors