How can I improve my Summarize Dax

[fradan]

Hello everyone,

I'm looking to create a table with four columns: "Asset group," "Location," "Location ID," and "Fault ID." The objective is to display, for each asset, the location and its corresponding location ID where the most faults have occurred, along with the corresponding number of faults. Unfortunately, the formula I've been using hasn't been providing the desired results:

AssetMaxFaultsTable =

SUMMARIZE (

    FILTER ( 'N10', 'N10'[IsLatestMonth] = TRUE ),

    'N10'[Asset category],

    'N10'[Asset Group],

    'N10'[Location],

    'N10'[Location ID],

    "TotalFaults",

    CALCULATE (

        COUNT ( 'N10'[Fault ID] ),

        FILTER ( 'N10', 'N10'[IsLatestMonth] = TRUE )

    )

)

How could I improve it?

Thank you very much!

[some_bih]

Hi @fradan try

ADDCOLUMNS(
FILTER (
SUMMARIZE ( 'N10',
'N10'[Asset category],
'N10'[Asset Group],
'N10'[Location],
'N10'[Location ID],
'N10'[IsLatestMonth]
),
'N10'[IsLatestMonth] = TRUE ),
"TotalFaults",
CALCULATE (
COUNT ( 'N10'[Fault ID] ),
FILTER ( 'N10', 'N10'[IsLatestMonth] = TRUE )
)
)