Forum Discussion

Rinat's avatar
Rinat
Helper I
4 years ago

Append multiple columns with filter enabled

Hello everyone. I'm trying to append multiple columns into 1 column without duplicates. I was able to write this to achieve that:   People sys_id = DISTINCT( UNION( VALUES(sn_control[owner]),...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

Rinat , Are these tables related. Which column will get filtered

You can use summarize(filter( sn_control , <filter>) ,sn_control[owner])

 

and union all such values

Rinat's avatar
Rinat
Helper I
4 years ago

yes. Tables are related. I want to filter by values in sn_profile[dv_u_regulatory_flag]. It can contain Value1, Value2 or Value1,Value2. Thank you for your help. 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Rinat 

     

    You need to write the logic of your filter into the formula. 

    Try this:

    People sys_id =
DISTINCT (
    FILTER (
        UNION (
            VALUES ( sn_control[owner] ),
            VALUES ( sn_control[u_control_performer] ),
            VALUES ( sn_control[u_evidence_collector] ),
            VALUES ( sn_control[u_control_executive] )
        ),
        RELATED ( sn_profile[dv_u_regulatory_flag] ) IN { value1, value2 }
    )
)

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

     

    • Rinat's avatar
      Rinat
      Helper I
      4 years ago

      Thank you, v-janeyg-msft. The suggested formula looks good, but it turned out I'm struggling to set up the right relationship since RELATED doesn't work properly so I guess I failed to set up the correct relationship. Can you help me with that? 

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        Hello Rinat 

         

        I only made up the code based on your text description, which may not match your actual situation, but I need you to provide more data details to modify it. Can you share some related columns sample data and relationships in two tables?

         

        Janey