topic Re: Making CALCULATETABLE filter by selected column in DAX Commands and Tips

Making CALCULATETABLE filter by selected column

powerx786 — Wed, 30 Nov 2022 03:26:47 GMT

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

Re: Making CALCULATETABLE filter by selected column

Jihwan_Kim — Wed, 30 Nov 2022 04:03:22 GMT

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.

Re: Making CALCULATETABLE filter by selected column

powerx786 — Wed, 30 Nov 2022 04:09:15 GMT

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.

Re: Making CALCULATETABLE filter by selected column

Jihwan_Kim — Wed, 30 Nov 2022 04:13:07 GMT

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" ) )

Re: Making CALCULATETABLE filter by selected column

powerx786 — Wed, 30 Nov 2022 04:44:29 GMT

I do not want to give an explicit value (Table1[Type] = "A") for the filter condition as I want to filter by the value selected in the filter visual

Re: Making CALCULATETABLE filter by selected column

tamerj1 — Wed, 30 Nov 2022 04:22:01 GMT

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.

Re: Making CALCULATETABLE filter by selected column

powerx786 — Wed, 30 Nov 2022 04:44:01 GMT

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)

Re: Making CALCULATETABLE filter by selected column

tamerj1 — Wed, 30 Nov 2022 09:25:06 GMT

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.