Finding a Max across months when using related tables

[Coriel-11]

Hi,
I have a DAX question. I have three tables,

The "Clicks" table has 3 columns "JoinID" "link name" & "unique clicks".

"JoinID"; "link name"; "unique clicks"

4567; Facebook; 23

3456; Twitter; 14

The "All Months" table has 2 columns "ID" (joined to "JoinID" from Clicks table) & "Date".

ID; Date

4567; 21/12/2022

3456; 04/04/2023

The "Dates" table has 2 columns "Date" (linked to Dates field in "All Months") and "MonthYear" which concatenates the month and year in the given date.

Date; MonthYr

12/12/2022; Dec 22
04/04/2023; Apr 23

> I want to get a measure that finds the maximum monthly total value (i.e. summarizing for MonthYear) for each "Link name".

I've tried a bunch of things, best guess was probably this

Hotspot = 
var tablx = SUMMARIZECOLUMNS(Dates[MonthYr],Clicks[link name],"Total Clicks",SUM(Clicks[unique clicks]))
var var1 = MAX(Clicks[link name])
var tably = FILTER(tablx,Clicks[link name]=var1)
var tablz = ADDCOLUMNS(tably, "Max", MAXX(tablx,[Total Clicks]))
Return
MAXX(tablz,[Max]) 

But this hasn't worked. I also tried creating a proper calculated table (still using summarizecolumns and that seemed to have different problems. Also I originally tried just unsing "Summarize" but couldn't get that to work I think because I was using different table.

Any ideas, suggestions would be much appreciated.

Matt

[SamInogic]

Hi @Coriel-11 ,

As per our understanding, you want to find maximum monthly clicks of unique link name. So you can try below DAX for the custom measure,

Hotspot =

VAR MaxMonthlyClicks =

    MAXX (

        SUMMARIZE (

            Dates,

            Dates[MonthYear],

            "Total Clicks", CALCULATE(SUM(Clicks[unique clicks]))

        ),

        [Total Clicks]

    )

RETURN

  if(ISBLANK( MaxMonthlyClicks),0, MaxMonthlyClicks)

Thanks!