Filtering Calculated DAX Table by Max Value in a column

[Anonymous]

Hey!

I have this problem that I want to filter a calculated table so that it only returns one row per customer where there is the maximum value in another column (Count column). I have tried multiple formulas for this one and also tried if ChatGBT could help me, but none has succeeded. Here is the formula that I have created by myself but it does not return the output that I want. The total assets 

Commodities per Customer =
VAR _table =
    ADDCOLUMNS (
        SUMMARIZE (
            MergedSites,
            'SVC by customer'[Business Partner],
            'SVC by customer'[Business Partner ID],
            MergedSites[Primary_Commodity]
        ),
        "Count",
            CALCULATE (
                COUNTROWS ( MergedSites )
            )
    )
RETURN
    FILTER (
        SUMMARIZE (
            _table,
            'SVC by customer'[Business Partner],
            MergedSites[Primary_Commodity],
            [Count]
        ),
        MAXX (
            _table,
            [Count]
        )
    )

 I would really appreciate it if someone could help me with this one.

[Ashish_Mathur]

Hi,

Why are you creating a calculated table?  Why not solve everything with a measure?  If my approach suits you, then share some data, describe the question and show the expected result.

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can try to use the following formula if it suitable for your requirement:

Commodities per Customer =
GROUPBY (
    SUMMARIZE (
        MergedSites,
        'SVC by customer'[Business Partner],
        'SVC by customer'[Business Partner ID],
        MergedSites[Primary_Commodity],
        "Csites", COUNTROWS ( MergedSites )
    ),
    [Business Partner],
    [Business Partner ID],
    [Primary_Commodity],
    "MCsites", MAXX ( CURRENTGROUP (), [Csites] )
)

If the above expression not helps, can you please share some dummy data that keep the raw data structure with expected results? It is hard to test and modify the expressions without any sample data.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng