Forum Discussion

rhcentennialh's avatar
rhcentennialh
Icon for Helper II rankHelper II
6 years ago
Solved

Calculated Column - Two Tables with a Filter or IF stament

What I would like to do is identify the "Names" that are on "Table 2" that are also on "Table 1" that have "Visit Type A".

 

Please let me know if you need further clarifcation

 

IDNameVisit Type ID NameCalculated Column
1111Name AVisit A 1111Name AVisit A
2222Name BVisit C 2222Name B 
3333Name CVisit F 3333Name C 
4444Name DVisit A 6666Name F 
    9999Name X 
    3333Name CVisit A
Need Help
Show and Tell
Tips and Tricks
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago
    Ah oke what threw us off was the incorrect value at the bottom of Table2 then, as Name C has no Visit A in table 1.
    This might be what you are looking for:
    CalcColumn =
    VAR curName = Table2[Name]
    RETURN
    IF( COUNTROWS( FILTER( Table1, Table1[Name] = curName && Table1[Visit Type] = "visit A") ) = 0, FALSE, TRUE)

    Typing on my phone, so be aware of typos 🙂

    If this answers your question, please accept it as the solution:)

4 Replies

  • rhcentennialh's avatar
    rhcentennialh
    Icon for Helper II rankHelper II
    6 years ago

    Thank you for the quick resposne. However i dont belive either of these are the solution i was looking for.

     

    What i am trying to do with the calculated column is the following

    1.) Check to see if an ID that is on Table 2 is on Table 1

    2.) If there is a match, then check to see if that ID/Name had a specific Visit Type (Example: Visit Type A) on Table 1

    3.) If they did then proceed to populate the calucalted colmun with "True" / "False" or "Visti Type"

     

    Let me know if you have any questions or further explination

     

     

    • JarroVGIT's avatar
      JarroVGIT
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago
      Ah oke what threw us off was the incorrect value at the bottom of Table2 then, as Name C has no Visit A in table 1.
      This might be what you are looking for:
      CalcColumn =
      VAR curName = Table2[Name]
      RETURN
      IF( COUNTROWS( FILTER( Table1, Table1[Name] = curName && Table1[Visit Type] = "visit A") ) = 0, FALSE, TRUE)

      Typing on my phone, so be aware of typos 🙂

      If this answers your question, please accept it as the solution:)
  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi rhcentennialh 

    try column 

    Calculated Column =
LOOKUPVALUE(
'Table1'[Visit Type],
'Table1'[Name],[Name],
'Table1'[ID],[ID]
)

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    LinkedIn

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    YOu can add a Calculated column to Table2 that returns a TRUE or FALSE based on your requirements.

     

    Column = IF(Table2[Calculated Column] = "Visit A" && CONTAINS(Table1, Table1[Name], Table2[ Name]), TRUE, FALSE)

    Results in the following table2:

    Let me know if this helps!

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂