How to Create a New crossjoin table with filter

[SrinathLM10]

I am trying to create a new table with crossjoin for table2 and table3 but based on a filter which is A. Similar to the example below.

table1      table 2   table 3                        New Table

A  |  B     A | C | ids      A | ids                    C   |  ids | A

______     _________      _______                    _____________

p  | a      p | x | 1          P | 1                       x   |  1  | p

q  | b      p | x | 2          P | 2                       x   |  2  | p

r   | c      p | y | 3          p | 3                       x   |  3  | p

s   | d     p | y | 2          q | 4                       x   |  4  | q

t   | e      q | x | 5          q | 5                       x   |  5  | q

                                                                  y   | 1  | p

                                                                  y   | 2  | p

                                                                  y   | 3  | p

[v-alq-msft]

Hi, @SrinathLM10 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

Table3:

You may create a calculated table as below.

NewTable = 
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(
            Table2,
            Table3
        ),
        [A2]=[A3]
    ),
    "C",[C],
    "ids",[ids2],
    "A",[A2]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@SrinathLM10 , logic or cross join in nor very clear. You can merge in power query Using Column A. In DAx You can do inner join.

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

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Let us know for last table