topic Re: MAXX Syntax and Locatin in DAX Commands and Tips

MAXX Syntax and Locatin

dmarkie — Wed, 13 Apr 2022 12:46:23 GMT

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"))

Re: MAXX Syntax and Locatin

amitchandak — Wed, 13 Apr 2022 13:07:41 GMT

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")) )

Re: MAXX Syntax and Locatin

AilleryO — Wed, 13 Apr 2022 13:10:46 GMT

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

Re: MAXX Syntax and Locatin

Whitewater100 — Wed, 13 Apr 2022 13:18:30 GMT

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 )

)

Re: MAXX Syntax and Locatin

dmarkie — Wed, 13 Apr 2022 13:23:19 GMT

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.

Re: MAXX Syntax and Locatin

dmarkie — Wed, 13 Apr 2022 14:01:52 GMT

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

Re: MAXX Syntax and Locatin

AilleryO — Wed, 13 Apr 2022 14:06:32 GMT

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 )

Re: MAXX Syntax and Locatin

dmarkie — Wed, 13 Apr 2022 15:30:37 GMT

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]) )

Re: MAXX Syntax and Locatin

AilleryO — Wed, 13 Apr 2022 15:37:07 GMT

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

Re: MAXX Syntax and Locatin

dmarkie — Wed, 13 Apr 2022 18:07:29 GMT

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

Re: MAXX Syntax and Locatin

AilleryO — Thu, 14 Apr 2022 09:39:04 GMT

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