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

       

       

      • Rinat's avatar
        Rinat
        Helper I
        4 years ago

        Sure v-janeyg-msft. Let me try to explain. All columns in the sn_control table that I'm trying to union contain sys_id's of people from sys_user table.

        To filter the sn_control table for reports I'm using dv_u_regulatory which located in sn_profile table

        I know I probably need to make a relation from sys_user table to sn_control where sys_id's of people are located. But there are 3 columns of people sys_id's and I don't know which one to pick. I know that you can't have 3 primary relationships. I thought to have 3 copies of sys_user table but I was concerned about the model size since the table is big.

        sys_user table has relation to people sys_id table which contains data from union

         

         

        So overall it looks like this:

         

         

         

         Hope it helps.

         

        Thank you