Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
2 years ago
Solved

Get column value where variable value matches another column value.

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

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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.

     

     

     

8 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    2 years ago

    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

     

  • StuartSmith's avatar
    StuartSmith
    Power Participant
    2 years ago

    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's avatar
      Daniel29195
      Community Champion
      2 years ago

      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's avatar
        StuartSmith
        Power Participant
        2 years ago

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