Filter a table in place by a measure from a related table

[kkanda]

I have two tables related by Item. Table1:

Item	Color
A	Green
B	Red
C	Blue
D	Brown

Table2:

Item	Qty
A	23
B	25
D	13
D	24
C	31
C	29
B	30
C	31

The actual tables are large having several rows. Table1 is displayed in the Visualization panel. When I select a row in Table 1, say containing item C, I want only rows with C as item in Table2 should show. Other rows must be filtered. This filtered table should appear below Table1 in the Visualization panel.  If no selection is done, the resultant table will be empty.

I used several ways to filter the table.. using CREATETABLE function. The code I used is below:

Sub_Table =
VAR sel_item = SELECTEDVALUE(Table1[Item], UNICHAR(32))
VAR res = CALCULATETABLE(
Table2,
FILTER(Table2, Table2[Item] = sel_item)
)
RETURN res

I am creating  a Sub_Table. When I click on any row in Table1 in the Visualization panel, the sel_item has a value and can be checked in a card. 

When I want to display Sub_Table as a table, nothing comes out. 

I have gone through several solutions appearing in the forums, but nothing is working for me. 

Is it that CREATETABLE function does not take any measures for filtering? If I give a hard coded value, such as "A" or "C", this function works. Or can I only use the calculated table function with measure as filter only for evaluating aggregates? 

[kkanda · Accepted solution]

Hi All... Thank you all for your helpful suggestions. As I mentioned previously, the issue was resolved primarily by my review of the data model. So there is no need to respond further to the query.

Thank you all again

Krishna Kandala

[rubayatyasmin]

Accept it as a solution then it will show resolved and no one will comment. 

Thanks. 

[kkanda]

@rubayatyasmin...Thank you 🙂

[v-stephen-msft]

Hi @kkanda ,

Here's an example for interaction between visuals.

When you click the row from Table1 visual, Table2 visual is filtered because of the relationship.

The relationship is important. It It determines the direction of the filter and the rules for the filtering. More details about relationship, refer to

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

You can also manage interaction, refer to

Change how visuals interact in a report - Power BI | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[kkanda]

Hello @nabandla and @,rubayatyasmin ,

Thank you both for the helpful suggestions. In fact, I developed a dashboard sometime ago that has this feature of selection of one row will filter the second table. I did not pay attention how it did. While reviewing my data model, I realized that the the date  range I selected for my Dim_Date covered the data range of Table1, but is falling short of the date range of Table2. That appears to be  the main reason why Table2 was not getting filtered as expected. When I expanded the time range of the Dim_Date table to cover the date range of the data in Table2, the filtering process happened. 

[rubayatyasmin]

sounds good that you figured out the culprit. 

If my assistance helped you in any way, appreciate the kudos. 👍

[rubayatyasmin]

Hi, @kkanda 

The issue you're facing is related to the context in which your calculations are being evaluated. In DAX, context is very important and it dictates how your formulas are evaluated.

CREATE TABLE and CALCULATETABLE functions create tables in a different context from your visuals, they are not dynamic and don't respond to selections in a visual. They are best suited for creating a table with static data at the beginning of the calculation. In your case, when you want to create a table that depends on a selection in a visual, you're better off using a measure and using a visualization tool that can display tables of measures.

The following measure should achieve your desired result:

Sub_Table =
CALCULATE(
CONCATENATEX(Table2, Table2[Qty], ", "),
KEEPFILTERS(Table1[Item] IN VALUES(Table2[Item]))
)

This measure will calculate a string containing all the Qty values for the selected Item, separated by commas. When no Item is selected, the measure will return BLANK and nothing will be shown.

Please note that this measure returns a string, not a table. DAX doesn't support table-valued measures because measures are expected to return a single value. If you want to display a table of Qty values for a selected Item, you'll need to use a matrix visual or a similar type of visual, where you would put Item on rows, and Qty in values.

Remember that measures are calculated for each cell in a visual, taking into account both the row context provided by the rows of the visual, and the filter context provided by any filters or slicers that are applied to the visual. Therefore, a measure like this would be responsive to a selection in a visual, unlike a table created with CREATE TABLE or CALCULATETABLE.

[nabandla]

In Power BI, you can simply use the built-in interaction between visuals to achieve what you're trying to do.

Follow the steps:

1. Place both `Table1` and `Table2` in the Visualization panel as tables.
2. Click on the paintbrush icon in the Visualizations panel to open the Format pane.
3. In the Format pane, expand the "Edit interactions" section.
4. With `Table1` selected, hover over `Table2` in the Visualizations panel and click on the "Filter" icon that appears.

Now, when you click on a row in `Table1`, `Table2` should automatically filter to only show rows with the selected item.

The `CALCULATETABLE` function and similar DAX functions create a table that can be used in other DAX formulas. They do not create a table that can be used directly in a visualization.

But if you want to create a measure that counts the number of rows in `Table2` for the selected item in `Table1`, you could do something like this:

```dax
Count of Selected Item =
VAR sel_item = SELECTEDVALUE(Table1[Item], UNICHAR(32))
RETURN
CALCULATE(COUNTROWS(Table2), Table2[Item] = sel_item)
```

This measure will return the number of rows in `Table2` that have the selected item from `Table1`. If no item is selected in `Table1`, it will return blank. You can use this measure in a card or other visualization to show the count of the selected item.