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.      ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi powerx786 
Apparently you can create calculated tables. In this case best is to create a common dimension table that filters both tables. 

Types =
DISTINCT (
    UNION ( ALLNOBLANKROW ( Table1[Type] ), ALLNOBLANKROW ( Table2[Type] ) )
)

 

Similarly you can have a dimension date table that filters the date in both tables. Therefore, you can place the date from the date table in the table visual.


Then create a one to many relationship between this table and both tables. You can use this column to slice by in the table visual. Then add the following measure to the same table visual.

Total Amount =
SUM ( Table1[Amount] ) + SUM ( Table2[Amount] )

Please note that the results of both tables will be merged together and aggregated at Type-Date level.

  • 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.