Forum Discussion

powerx786's avatar
powerx786
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Making CALCULATETABLE filter by selected column

I have data like this in two tables as Table1 and Table2, with same columns      In my PowerBI report, I have two filters on Table1's Type and Table2's Type columns like below.      ...
powerx786's avatar
powerx786
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

Unfortunately I do not want to create a common dimension that filters both tables, as the idea is to be able to filter both tables using separate filtering conditions. 

 

In the example I just so happened to show one column with same name from both tables, for simplicity. But there could be different filters with different values on either table for the sake of comparing the final calculated table difference between Table1 and Table2 (I will also add a column to indicate if the particular row comes from Table1 or Table2)

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi powerx786 
I'm just thinking outloud trying to find a reasonable solution without having to dive deep into complex DAX.

Here is one approach.

  • Let's say that we know that Table1 has around 120K rows, then you can add (in the query editor) an index column to Table1 starts from 1 then add an index column to Table2 starts from 200,000 just to keep a room for future data expansion of Table1.
  • Now we can create an Index Table:

 

Index Table = SELECTCOLUMNS ( GENERATESERIES ( 1, MAX ( Table2[Index] ), 1 ), "Index", [Value] )

 

  • Create a relationship between the index table and both tables.
  • Place the index column of the index table in a table visual.
  • Now you should be able to combine any column from both tables using measures as follows:

 

For text columns 

 

Measure1 = SELECTEDVALUE ( Table1[Column1] ) & SELECTEDVALUE ( Table2[Column1] )

 

For date or decimal data type columns

 

Measure2 = SELECTEDVALUE ( Table1[Column2] ) + SELECTEDVALUE ( Table2[Column2] )

 

 

  • Please let me know if this aproach works with you.