Creating new tables from another collumns table and filtring it

[yousseffaite]

Hi, I am looking for resolving little issue in power bi since i am not so familiar with the application speacilly using queries.
I wanted to merge two columns from the same table into a new table then to delete all the duplicas
Tables : Interactions:
Collumn 1 : A_handel
Collumn 2 : B_handel

The result should help me to apply filters that could help me to get the number of interaction that are going from any users from the collumn A_handel and same apply for the user that interact from the collumn B_handel

Thank you,
Y.</p>

[v-huizhn-msft]

Hi @yousseffaite,

Based on my understanding, I create the following sample table.

sample table
Then click "New Table" under Modeling on Home page, type the statement below. 

New_Table = 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Interactions, "handel", Interactions[A_handel] ),
        SELECTCOLUMNS ( Interactions, "handel", Interactions[B_handel] )
    )
)

You can merge two columns from the same table into a new table then to delete all the duplicas. And you can get the following expected result.



Please let me know if you have other issue.

Best Regards,
Angelia

[yousseffaite]

Hi @v-huizhn-msft,

It worked very well thank you ! for the second part of the creation and wanted to create a mesure that calculates every iteration i mean for example : let's say we have a table that looks like this 

let's Ahandel is the source and Bhandel is the target 

  

I dont' know if it's possible through powerbi to calculate this but i want a dynamic calculation of every interaction that went from the Ahandel to the B handel for every user and from B handel to Ahandel for every user

Like A source (Ahandel)=  sum of ( sum (from A to B) + ( sum (from A to C)) + ( sum (from A to N) ....

And the A Target (Bhandel) =  sum of ( sum (from B to A) + ( sum (from C to A)) + ( sum (from D to A) + ( sum (from N to A)....

For every user in the Ahandle and the Bhandel

[v-huizhn-msft · Accepted solution]

Hi @yousseffaite,

From what I know, we can't such a measure as what you want. Because the basic calculation item in Power BI is a column rather than row in excel. 

Best Regards,
Angelia

[yousseffaite]

@v-huizhn-msft Thank you very much for your time !