Get column value where variable value matches another column value.

[StuartSmith]

I have a calculated column where I can creating a specific calculation similar to below...

Test =

...

VAR BackfillRequiredWithinDays = CALCULATE(MIN('Table: Absence Master List'[12) Backfilled Required within Days]))
VAR SelectedDate = "AbsenceDate" where the value of "BackfillRequiredWithinDays" equals the "Rank" value.
Return

SelectedDate

ID		AbsenceDate		Name		Rank		SelectedDate
1		01/01/2024		John Doe		1
1		02/01/2024		John Doe		2
1		03/01/2024		John Doe		3
1		04/01/2024		John Doe		4
2		01/01/2024		Jane Doe		1
2		02/01/2024		Jane Doe		2
2		03/01/2024		Jane Doe		3

Thanks in advance

[StuartSmith]

So to simplfy it (as can develop it later), looking something similar to... but the below isnt working.

VAR SelectedDate =  SELECTEDVALUE('Table'[AbsenceDate], FILTER('Table', 'Table'[RANK] = 3))

 with the result...

ID		AbsenceDate		Name		Rank		SelectedDate
1		01/01/2024		John Doe		1		03/01/2024
1		02/01/2024		John Doe		2		03/01/2024
1		03/01/2024		John Doe		3		03/01/2024
1		04/01/2024		John Doe		4		03/01/2024
2		07/01/2024		Jane Doe		1		09/01/2024
2		08/01/2024		Jane Doe		2		09/01/2024
2		09/01/2024		Jane Doe		3		09/01/2024

Thanks

[Daniel29195]

@StuartSmith 

Column =

var ids =  table10[ID]

return

SELECTCOLUMNS(

FILTER(

    table10,

    table10[ID]  = ids && table10[Rank] = 3

),

[AbsenceDate]

)

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[StuartSmith]

Thanks, that seemed to work, although before trying on my actual table, I have recreated the test table and although your code displays the correct filtered date, the code has an error (parameter is not correct type) and I dont know why.  Any ideas? What data type did you have for "AbsenceDate", mine is "Date".

[Daniel29195 · Accepted solution]

@StuartSmith 

date. 

you can change the type to date for the new caluclated column . 

but leaving it this way also works and wont affect anything . 

for the red line, 

this is an intellisense bug, i guess

however you can get rid of it : 

Column =

var ids =  table10[ID]

return

SELECTCOLUMNS(

FILTER(

    table10,

    table10[ID]  = ids && table10[Rank] = 3

),

"@col" , [AbsenceDate]

)

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly.

[StuartSmith]

Sorry to trouble further, but developing your solution,  I want to create a final variable for the column that would check if there were any blank rows for each id that occure after the date value identified in the "RequiredByDate" and put 1 if a there was a blank row and 0 if not.  So ID1 would be true as there is a blank row after row 3, but ID2 would be false.  Hope that makes sence and you are able to point me in the right direction.  Thanks in advance.

[StuartSmith]

Many thanks 👍

[sayaliredij]

Hi @StuartSmith 

It would help to understand the first part of the DAX

VAR BackfillRequiredWithinDays = CALCULATE(MIN('Table: Absence Master List'[12) Backfilled Required within Days]))

How this other table look like?

Thanks,

Sayali

[Daniel29195]

@StuartSmith 
can you explain the desired output that you want to achieve.