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]),
VALUES(sn_control[u_control_performer]),
VALUES(sn_control[u_evidence_collector]),
VALUES(sn_control[u_control_executive])
)
)
 
The issue is that before I can union columns I need to apply a filter based on another table's values. It's a column in another table which can be: Value1, Value2 or Value1,Value2.
Trying to figure out where I need to put a filter to wrap Union in.
 
Thank you a lot for your help 🙂
Need Help

8 Replies

  • 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