Append multiple columns with filter enabled

[Rinat]

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 🙂

[amitchandak]

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

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]

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]

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]

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]

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

[v-janeyg-msft]

Hi @Rinat 

I'm not trying to know the relationships in tables, it's useless for me to help you. Can you share some related columns sample data and relationships in two tables? The first thing I need is sample data. . .

[Rinat]

@v-janeyg-msft sorry for radio silence. I need to figure out how to anonimyze sampe data and how to make it real sample because now it's a live data from the source. I'll try to figure out something. Thank you for your help