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. 

 

 

With these two filters selected I want to UNION the resultant filtered result set from Table1 and Table2 as the output. 

 

So I created these two DAX expressions to test the result:

 

Table3 = UNION(Table1, Table2)
 
Table4 =
UNION(
 CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Type])),
 CALCULATETABLE(Table2, ALLEXCEPT(Table2, Table2[Type]))
)
 
Even though the Type filter for both Table1 and Table2 is selected as A, I still see all the rows in my Table3 and Table4 result set. I need a calculation where I will see the UNION of the two filtered sets instead of the whole unfiltered UNION e.g. with the selections in the visuals as Type=="A", I want to see rows with only A in the UINION;ed data. Please help with the DAX expression

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but in my opinion, slicer selection from the visualization is not influencing the result when creating physical calculated table. If you want to make it happen, one of ways is to insert the condition directly into the formula to create physical calculated table.

    I hope this helps.

    Thanks.

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

      There is nothing in the data model except two separate tables. There are no further relationships. The slicer in the report is directly referencing the column in the table. 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        Please try something like below to create a calculated table whether it suits your requirement.

         

        Table4 =
UNION (
    CALCULATETABLE ( Table1, Table1[Type] = "A" ),
    CALCULATETABLE ( Table2, Table2[Type] = "A" )
)

         

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