Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkanda
Resolver I
Resolver I

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

I have two tables related by Item. Table1:

ItemColor
AGreen
BRed
CBlue
DBrown

Table2:

ItemQty
A23
B25
D13
D24
C31
C29
B30
C31

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? 

 

1 ACCEPTED SOLUTION
kkanda
Resolver I
Resolver I

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

View solution in original post

8 REPLIES 8
kkanda
Resolver I
Resolver I

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

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

 

Thanks. 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


@rubayatyasmin...Thank you 🙂

v-stephen-msft
Community Support
Community Support

Hi @kkanda ,

 

Here's an example for interaction between visuals.

vstephenmsft_0-1689835873175.png

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

vstephenmsft_1-1689835922500.png

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

vstephenmsft_2-1689836054601.png

 

 

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
Resolver I
Resolver I

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. 

sounds good that you figured out the culprit. 

 

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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


rubayatyasmin
Super User
Super User

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.

 

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


nabandla
New Member

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.