DAX code : get a value from the last row of a filtered table

[Anonymous]

Hi,

I have a calculated column where I want to have a binary true/false depending on another filtered table column number

1. Filter the table following a particuliar string
2. Get the last row of that filtered table
3. Find a value in a specified column
4. If > 7, then true and else then false

Data structure

Table1:

Name	Date	Score
A	05/01/2022	8
B	06/01/2022	5
B	07/01/2022	10
A	07/05/2022	3

Table2:

Name	Calculated column : Last score is > 7
A	No
B	Yes

Could anyone help me get the DAX code for the Calculated column in Table2 right ?

All I have for now is to filter Table1 for only A rows.

Thanks

James

[Shishir22 · Accepted solution]

Hello @Anonymous ,

Give relationship between two tables based on Name and then please try calculated column as-

Column = 
VAR _MaxDate = MAXX(filter(RELATEDTABLE(Table1),Table1[Name]=EARLIER(Table2[Name])),Table1[Date])
VAR _Value= CALCULATE(FIRSTNONBLANK(Table1[Score],0),filter(RELATEDTABLE(Table1),Table1[Date]=_MaxDate))
Return
IF(_Value>7,"Yes","No")

Please mark it as solution if it solves your issue. Kudos are also appreciated.

Cheers,

Shishir

[Anonymous]

I can't put

filter(RELATEDTABLE(Table1),Table1[Date]=_MaxDate)

in var _Value, it doesn't show "Table1[Date]" as criteria

I have added a picture showing the link between my table : 

In orange "derniere_note" is the last note of the left table, depending on the zone.

In yellow the link between the table : left tables has multiple entries corresponding to on entry in the middle table (2 columsn highlighted because I can do the link with both columns)

In green the columns we can use to rank the entries, the ID --> ID+1 each time a new row is made

[Anonymous]

EDIT :

Column = 
VAR _MaxDate = MAXX(filter(RELATEDTABLE(Table1),Table1[Name]=EARLIER(Table2[Name])),Table1[Date])
VAR _Value= CALCULATE(FIRSTNONBLANK(Table1[Score],0),filter(RELATEDTABLE(Table1),Table1[Date]=_MaxDate))
Return
IF(_Value>7,"Yes","No")

does not work becaus einstead of related table you shouldnjust use Table, as :

Column = 
VAR _MaxDate = MAXX(filter(RELATEDTABLE(Table1),Table1[Name]=EARLIER(Table2[Name])),Table1[Date])
VAR _Value= CALCULATE(FIRSTNONBLANK(Table1[Score],0),filter(Table1,Table1[Date]=_MaxDate))
Return
IF(_Value>7,"Yes","No")

@Shishir22 , please edit your post so when new people come see they get the accepted answer as correct directly.

thanks everyone 🙂

[ribisht17]

@Anonymous 

STEP1

RANK COL = RANKX(FILTER( 'True False','True False'[Name]=EARLIER('True False'[Name])),'True False'[Date])

STEP2

Latest > 7 = IF('True False'[Score] > 7 && 'True False'[RANK COL]=1,"Yes","No")

STEP 3

Solution = MAXX(FILTER(ALL('True False'),'True False'[Name]=EARLIER('True False'[Name])) ,'True False'[Latest > 7])

Regards,

Ritesh

[Anonymous]

Could you explain the code ? I copied paste it and changed the columns names to my columns names, and it doesn't seem to work.

I have added a picture showing the link between my table : 

In orange "derniere_note" is the last note of the left table, depending on the zone.

In yellow the link between the table : left tables has multiple entries corresponding to on entry in the middle table (2 columsn highlighted because I can do the link with both columns)

In green the columns we can use to rank the entries, the ID --> ID+1 each time a new row is made