Forum Discussion

nemo's avatar
nemo
Helper I
3 years ago
Solved

Create a table based on a condition

I have the following table

ScoreCommentPerson
1 p1
2 p1
3 p1
4 p1
 abcp1
2 p2
3 p2
4 p2
5 p2
  p2

 

How can I create another table that contains only rows for person p1 who provided a comment? The desired output should look like this

ScoreCommentPerson
1 p1
2 p1
3 p1
4 p1
 abcp1

 

Thank you.

Need Help
  • PVO3's avatar
    PVO3
    3 years ago

    Hi, 

    Try this:

     

    Table = 

    VAR _tbl = SUMMARIZE(FILTER('Table', NOT ISBLANK('Table'[Comment])), 'Table'[Person])
    RETURN

    CALCULATETABLE(
        'Table',
        'Table'[Person] IN _tbl
    )

2 Replies

  • PVO3's avatar
    PVO3
    Impactful Individual
    3 years ago

    Hi, 

    Try this:

     

    Table = 

    VAR _tbl = SUMMARIZE(FILTER('Table', NOT ISBLANK('Table'[Comment])), 'Table'[Person])
    RETURN

    CALCULATETABLE(
        'Table',
        'Table'[Person] IN _tbl
    )
    • nemo's avatar
      nemo
      Helper I
      3 years ago

      Hi PVO3 , thank you for a clear and easy to follow solution. Much appreciated.

      I tried to use the following formula hoping it would return the desired result

      Table 2 = 
VAR comments = CALCULATETABLE( VALUES('Table'[Person]), 'Table'[Comment] <> "") 
VAR tbl = FILTER('Table', 'Table'[Person] = comments)
RETURN tbl

      However, it resulted in this error 

      <ccon>A table of multiple values was supplied where a single value was expected.</ccon>

      Could you please tell me what that error meant? What's wrong with my formula?