How to create custom table with distincts columns from 3 different tables?

[Anmolgan]

I need to build custom tables with 3 columns that are present in 3 different tables for example, I have column A in Table1, Table2, Table3, likewise I have column B in Table 1, Table 2 and Table3, I want to take the distincts from all these tables and create a single table so that I ca build relationship among those 3 tables with the bridge tables can calculate my sum, which is basically coming from 3 different tables.

[v-kelly-msft]

Hi @Anmolgan ,

I'm not sure whether your table 1,2,3 has the similar structure as below:

And whether the below output is your expected result?

If so ,you just need a dax expression to create a new table:

Table =
VAR K =
    SUMMARIZE (
        'Table 1',
        'Table 1'[Column A],
        "Column B1", DISTINCT ( 'Table 1'[Column B] )
    )
RETURN
    ADDCOLUMNS (
        K,
        "Column B2", LOOKUPVALUE ( 'Table 2'[Column B], 'Table 2'[Column A], 'Table 1'[Column A] ),
        "Column B3", LOOKUPVALUE ( 'Table 3'[Column B], 'Table 3'[Column A], 'Table 1'[Column A] )
    )

For the related .pbix file,pls click here.

If above isnt what you need,pls advise me your table structure and your expected output.

Best Regards,
Kelly

[Anmolgan]

@v-kelly-msft Thanks for the response, I will share the pbix in your private chat, also will give you in depth scenerio that we have and the issue that we are facing, looking forward to your reply.

[Anmolgan]

@v-kelly-msft 

I have 4 tables  "ar_tran_rcpt" "ar_tran_room" "ar_tran_ancl" "ar_bal"  i am extracting amount from all these tables. All 4 tables have similar meaning columns but with different names, like in ar_tran_rcpt the column name is "artyp" and in ar_tran_room it is "res_artyp" and ar_tran_ancl it is "chrgcd_artyp". Their meaning is same but names is different.

i want 4 columns in my slicers which would affect the amount present from all the 4 tables. following are the columns from 4 tables: 1) ar_tran_rcpt column names - cono, artyp, resno, svc_date

2) ar_tran_room column names - cono, res_artyp, resno, gl_period

3)ar_tran_ancl column names - cono, chrgcd_artyp, resno, gl_period.

4) ar_bal column names - cono, artyp, resno.

How can i build slicers in such a way that it will affect the amount. like now if i take a column named cono from ar_trab_rcpt table then it affects the amount only for that table and not the rest .

Sending you the PBIX, please check your private chat.

[Anmolgan]

@v-kelly-msft 

here after applying slicer for cono = 02 i get correct amt for cash which is 202524410.60, but the amount which im getting in total revenue which is room revenue is wrong , the value there should be 18,44,40,708.12‬, and for total amt(chrgcd_artyp) the value should be 9156581.08, total_amt(coins_artyp) should be 2070782.79. the slicer for cono is only affecting amt which is from ar_tran_rcpt table as i have took all these slicers from that table itself, 

new_amt_bal should be 2361728.62

[amitchandak]

See if you can merge with the help of this link. Then you can use summarize od distinct to do that

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Another one is costly cross join and distinct/summarize on top of it.

SUMMARIZE(CROSSJOIN('Item',Geography),'Item'[Brand],Geography[City])
or
DISTINCT(SUMMARIZE(CROSSJOIN('Item',Geography),'Item'[Brand],Geography[City]))

Also refer, check for various solution that has been discussed.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anmolgan]

@amitchandak Neither Merge/Append will help me in my case any other things I can try?

[amitchandak]

Have you tried cross join and summarize option I shared? Share your relationship diagram