MAXX Syntax and Locatin

[dmarkie]

Hi.  Newer to DAX and still learning.  I've read much and vids on Maxx but am having difficulty in achieving the desired outcome in this table creation.   I have two tables linked on the MasterID field.  The Dimesion Table/One side, is Master while the Many Relation/Fact table is Detail.  I am wanting to find the most current entry date (field name DATE) for each MasterID that exists in the Detail Table.  I am also filtering the detail table on a few fields as you can see.  This code works ok but produces ALL the records for each MasterID entry DATE.  I only want the most recent which is where i'm having problems.  I have placed the MAXX function in this DAX in a number of ways which results in syntax or other errors.  Any help is appreciated.

Thx

Dave

Table = FILTER (RELATEDTABLE(Detail), NOT ISBLANK('Detail'[VEHICLE ID]) && Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof"))

[Whitewater100]

Hi:

A separate DateTable is recommended, marked as Date Table (Table Tools) and connected as the one-side to your fact table. Hopeflly your Fact Table has a date field that you will report off of. Usually transaction date, order date, etc.I'll paste a DAX Date Table you can use (New Table Option on Ribbon) below.

You can use the LASTNONBLANK function = LASTNONBLANK(Dates[Date], [Master ID])

This will give you the last date in your Detail table where there is a Master ID.

You can use MAX too if you are filtering to get the last Master ID with conditions:

Last ID = CALCULATE (

    MAX ( Detail[Date] ),

    FILTER (

        ALL ( Detail ),

        Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof")

    ),

    VALUES ( Detail[MasterID] )

)​

I hope this helps.

DAX DATE Calendar

DATES =

  GENERATE (

    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),

    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday   

    VAR currentDay = [Date]

    VAR days = DAY( currentDay )

    VAR months = MONTH ( currentDay )

    VAR years = YEAR ( currentDay )

    VAR nowYear = YEAR( TODAY() )

    VAR nowMonth = MONTH( TODAY() )

    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1

    VAR todayNum = WEEKDAY( TODAY() )

    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )

  RETURN ROW (

    "day", days,

    "month", months,

    "year", years,

    "day index", dayIndex,

    "week index", weekIndex,

    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),

    "year index", INT( years - nowYear )

  )

)

[AilleryO]

Hi,

Did you try to use ADDCOLUMNS, to create the column with the MAXX([Date]) function ?

Something like :

VAR TempTable = FILTER (RELATEDTABLE(Detail), NOT ISBLANK('Detail'[VEHICLE ID]) && Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof"))
RETURN
ADDCOLUMNS( TempTable , "Last Date" , MAXX( Date ) )

Let us know if it works

[dmarkie]

Thank you.

I had not tried Adding a column so i attempted.  The error reported Too Few agruments were passed to MAXX function.  2 is min req'd

[AilleryO]

My mistake, the first argument is the table and the second one the column so it should have been in my formula :

MAXX( Detail , Date ) 

[dmarkie]

Thanks getting close....this yeilded the new column however the values are all the same (4/6/2022) for all the records for all the MasterIDs.  

Here's the DAX as i have entered;

Table = VAR TempTable = FILTER (RELATEDTABLE(Detail), NOT ISBLANK('Detail'[VEHICLE ID]) && Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof"))

RETURN

ADDCOLUMNS( TempTable , "Last Date" , MAXX( 'Detail', [Date]) )

[AilleryO]

You can create one more VAR to store the MasterID.

VAR CurrentMasterID = MAX( MasterID )

and add a condition to the formula for table
&&MasterID = CurrentMasterID

[dmarkie]

sorry i'm not clear on the last step of adding the below condition to the formula for the table.  The only formula i have is the DAX formula we are working on.  Sorry for not understanding.

&&MasterID = CurrentMasterID

[AilleryO · Accepted solution]

No problem, sorry for not being more specific and anyway I was wrong, you should add the filter when you calulate max per MasterID, like :

Table = 
VAR CurrentMasterID = MAX( [MasterID] )
VAR TempTable = FILTER (RELATEDTABLE(Detail), NOT ISBLANK('Detail'[VEHICLE ID]) && Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof") )

RETURN

ADDCOLUMNS( TempTable , "Last Date" , CALCULATE(MAXX( 'Detail', [Date]) , MasterID = CurrentMasterID  )

Let us know if it works

[amitchandak]

@dmarkie , Are you creating a measure or column.

For measure, you can try like

calculate(Max(Master[Value]), FILTER ((Detail), NOT ISBLANK('Detail'[VEHICLE ID]) && Detail[MasterID]>250 && CONTAINSSTRING(Detail[Description], "lof")) )

[dmarkie]

In the Max(Master[Value]), there is not a DATE field in the MASTER table which is the 1 side of the relation.  The DATE field which i need to retrieve the most recent date for each unique MasterID is located in the DETAIL table on the Many side.