Replicate Excel formulae in Dax column

[jaltoft]

Can anyone help me replicate this formulae in DAX?

=IF([@[Open at Week End]]="N","",IFERROR(VLOOKUP([Ticket No.]],'Assessments'!L:Z,15,FALSE),""))

This is my attempt that returns different values -

Assessment Position =

CALCULATE (

FIRSTNONBLANK ( assessments[Position of Assessment], TRUE()),

FILTER (

assessmentswk,

assessmentswk[ReportWeekSunday] = Opencasesweekendsunday[ReportWeekSunday]

&& assessmentswk[Referral Ticket No.] = Opencasesweekendsunday[Ticket No.] )

)

In the Power bi I have a Report week Sunday which is the upload date.

[jaltoft]

Hello ,

An example would be-

Table 1 (Should only be one ticket number per week in table 1).

Upload date           Ticket number       Case state

24/01/2021               454545454          Wanted output from table 2

17/04/2021                11111111           Wanted output from table 2

Table 2 (We can have more than one case open and need to find if Open or closed or other)

24/04/2021               454545454          Open

24/04/2021               454545454          Open

17/01/2021                11111111           Closed

17/01/2021                11111111           Closed

17/01/2021                11111111           Closed

@amitchandak @V-pazhen-msft 

Hope this helps?

[jaltoft]

Hello @amitchandak @V-pazhen-msft this is the part of the formula I need to replicate if it wasnt clear?

=VLOOKUP([Ticket No.]],'Assessments'!L:Z,15,FALSE)

The issue is with the formulae I have tried in DAX I have either got an incorrect result or using a LOOKUPVALUE with the case state as the result column name, and search column as ticket number from table 1, Search value as ticket number from table 2,Upload date from table 1 as search column 2, Upload date from table 2 as search value 2) this isnt working as in table 2 it has multiple dates with ticket numbers.

I have also tried using First non blank which seems to bring the wrong result.

[V-pazhen-msft]

@jaltoft 
I agree, we need sample tables and please explain the logic as well. 

Paul

[amitchandak]

@jaltoft ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.