For a group of values, find the value with the maximum occurrences out of the group, and list value

[admincaleb007]

Ultimately, I need to contact the least amount of people for all assets.

I have a list of contacts for assets. Each asset may have 1 or more contact. Each contact may be responsible for many assets. I am looking for a way to transform my table so that I can reach only 1 contact per asset, making sure to contact only one person per asset.

Example:

Asset#	Contact(s)	What Do I need via Powerquery in PowerBI? Would I contact this user for this asset#?
0123abcd	user1	Yes, as user1 was listed 4 times, which is the maximum occurrences for this asset number
0123abcd	user2	No, as user2 was only listed 1 time, and is lower than the maximum occurrences for this asset number
0123abcd	user3	No, as user3 was only listed 2 times, and is lower than the maximum occurrences for this asset number
0123abcd	user4	No, as user4 was only listed 1 time, and is lower than the maximum occurrences for this asset number
0123abcd	user5	No, as user5 was only listed 1 time, and is lower than the maximum occurrences for this asset number
4567efgh	user1	Yes, as user1 was listed 4 times, which is the maximum occurrences for this asset number
4567efgh	user3	No, as user3 was only listed 2 times, and is lower than the maximum occurrences for this asset number
4567efgh	user6	No, as user6 was only listed 1 time, and is lower than the maximum occurrences for this asset number
4567efgh	user7	No, as user7 was only listed 3 times, and is lower than the maximum occurrences for this asset number
4567efgh	user8	No, as user8 was only listed 1 time, and is lower than the maximum occurrences for this asset number
8901ijab	user1	Yes, as user1 was listed 4 times, which is the maximum occurrences for this asset number
8901ijab	user7	No, as user7 was only listed 3 times, and is lower than the maximum occurrences for this asset number
2345cdef	user7	Yes, as user7 was listed 3 times, which is the maximum occurrences for this asset number
2345cdef	user10	No, as user10 was only listed one time, and is lower than the maximum occurrences for this asset number
2345cdef	user11	No, as user11 was only listed one time, and is lower than the maximum occurrences for this asset number
6789ghij	user1	Yes, as user1 was listed 4 times, which is the maximum occurrences for this asset number

I used bold to separate the "groupings" of asset#s.  I underlined the contacts that I would contact.

For the example, I would be able to summarize the modified table by the following:

Across 5 distinct assets please:

Contact user1 for assets 0123abcd, 4567efgh, 8901ijab, 6789ghij
Contact user7 for asset2345cdef
This will cover 5/5 (100%) of the asset contacts.

Please let me know if I can further help!  And thank you!

[admincaleb007]

I am currently testing a measure for what I am looking for, though I feel like it could be wrong.  If anyone has input, that would be useful.

Measure = var a  = CALCULATE(MAX('Table'[Contact]),ALLEXCEPT('Table','Table'[Asset#]))
Return
IF(MAX('Table'[Contact])=a,1,0)

I then believe I can filter by this measure.  1 would be my contact, and 0 would be an "additional" contact, but not used.

[v-shex-msft]

Hi @admincaleb007,

You can use the following calculate table formal to create a new table with expected results:

NewTable = 
VAR summary =
    SUMMARIZE (
        'Table',
        [Contact(s)],
        "Asset Count", COUNT ( 'Table'[Asset#] ),
        "Asset", CONCATENATEX ( VALUES ( 'Table'[Asset#] ), [Asset#], "|" )
    )
VAR filtered =
    FILTER ( summary, [Asset Count] > 1 )
RETURN
    SELECTCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                filtered,
                "Asset#",
                    VAR prevAsset =
                        CONCATENATEX (
                            FILTER ( filtered, [Asset Count] > EARLIER ( [Asset Count] ) ),
                            [Asset],
                            "|"
                        )
                    VAR currList =
                        SELECTCOLUMNS (
                            ADDCOLUMNS (
                                GENERATESERIES ( 1, PATHLENGTH ( [Asset] ), 1 ),
                                "AssetText", PATHITEM ( [Asset], [Value] )
                            ),
                            "AssetText", [AssetText]
                        )
                    RETURN
                        IF (
                            prevAsset <> BLANK (),
                            VAR prevList =
                                SELECTCOLUMNS (
                                    ADDCOLUMNS (
                                        GENERATESERIES ( 1, PATHLENGTH ( prevAsset ), 1 ),
                                        "AssetText", PATHITEM ( prevAsset, [Value] )
                                    ),
                                    "AssetText", [AssetText]
                                )
                            RETURN
                                CONCATENATEX ( EXCEPT ( currList, prevList ), [AssetText], "," ),
                            CONCATENATEX ( currList, [AssetText], "," )
                        )
            ),
            [Asset#] <> BLANK ()
        ),
        "Contact(s)", [Contact(s)],
        "Asset Count", [Asset Count],
        "Asset#", [Asset#]
    )

Regards,

Xiaoxin Sheng

[admincaleb007]

Thanks for this.

Seems that your list pulls back a different list than my logic would, which is a positive.

Unfortunately, it does seem to list multiple email addresses if the "Asset Count" for a "contact" is equal.  So I have a list of 2 emails (or more) for the same set of assets.

For example:

Responsible.A -> 11 assets

Responsible.B -> 11 assets

The table output includes both emails instead of only one.

I didn't think this would be the cast, or I would have posted it in the initial post, sorry about that.

Additionally, is there any way to remove a specific contact?  I know there are a few of these contacts that are no longer accurate, so I would need to have a list of contacts to "ignore".  If they are to be ignored, then the next best contact needs to be evaluated while keeping the same end-goal intact which is to have a contact for each asset, while having the fewest contacts possible.

[v-shex-msft]

HI @admincaleb007,

My formula is used to get unique items that do not exist in the previous records, but it sounds not suitable for your requirement.

If you want to exclude specific records, I'd like to suggest you create a table with not need records, then you can use the DAX expression to extract from that table to compare and exclude from the current item lists.

EXCEPT function (DAX) - DAX | Microsoft Docs
Regards,

Xiaoxin Sheng