Forum Discussion

mshashmi's avatar
mshashmi
Frequent Visitor
9 years ago
Solved

Using where clause

Hi   I want to make a visual where I want something like 'where' clause.  Suppose I have two tables and I want those rows from table1 where table1.column1=table2.column3   Can it be done using c...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi mshashmi,

    You can use CROSSJOIN function as follows.


    First, change your column header name, which make them different in different tables. Because, crossjoin function is unsupported for same column names.



    Then, click "New Table" under Modeling on Home page, type the following formula, you will get expected result.

    Table = FILTER(CROSSJOIN(Table1,Table2),Table1[Tab1-Column2]=Table2[Tab2-Column2])



    Best Regards,
    Angelia

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

mshashmi  

 

can you be more specific about what you are trying to do?  examples of data, screenshots etc.

mshashmi's avatar
mshashmi
Frequent Visitor
9 years ago

vanessafvg

 

I have to make a table visual. I have around 10 tables. I have joined all tables without any issue. Now for my report, I need to use a condition in which I need to display those rows whose value in one table eqauls that in another table

 

Table1

 

Column1     Column2

ABC                1

QWE               2

XVC                 3

KFH                 6

 

Table2

 

Column1     Column2

123                   2

646                   6

987                   1

 

Now I want complete data of all columns of table1 where table1.column2=table2.column2

ie first, second and fourth row should be displayed as only that value is present in table2.column2

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi mshashmi,

    You can use CROSSJOIN function as follows.


    First, change your column header name, which make them different in different tables. Because, crossjoin function is unsupported for same column names.



    Then, click "New Table" under Modeling on Home page, type the following formula, you will get expected result.

    Table = FILTER(CROSSJOIN(Table1,Table2),Table1[Tab1-Column2]=Table2[Tab2-Column2])



    Best Regards,
    Angelia