Hi, I have three table with the following data and relationships1. Epic2. Story3. Task4. Relationships I have the following visuals setup:A. Each table filters the other two tablesB. The issue is as follows:B.1 When I click on E1 from the first table then Story Number and Task Number is filtered - So far good.B.2 When I click on S2, then Task Number is filtered but both Story Number reverts to the full list and Epic Number also goes back to the original state! What I want is that on clicking S2 isEpic Number should still continue to highlight E1Story Number should still show visual B.1Task Number should show T4..T6So basically selection of a row in the Story table seems to revert the filtering on the Epic table and the filtering earlier applied to itself (Story table).

Hi, I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file. What I did was I create a measure like below, and put it into the filter pane and create a condition like below for the two visualizations. Task Number measure: =IF ( MAXX ( Task, RELATED ( Epic[Epic Number] ) ) = BLANK (), 0, 1 )

Hi Kim, Thanks for the response. The issue is that when I select S2 in the second table, the visual shows all the values i.e. S1..S6 instead of S1..S3 Raghu

I would use power query to merge all three tables into a single "task" dimension. Each row would be a task but also contain columns for story and epic. It's then much easier to build hierarchies. Currently you have a snowflake, while power bi likes star schema. Although it looks like you'd be duplicating lots of data, the way verticac works results in the model being small.

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema#snowflake-dimensions

Even if I merge all the tables using Power Query into 1, if I have three visuals as per above, I have the same problem.

Table row selection cause filtering to break

14 Replies

Jihwan_Kim
Super User
4 years ago
Hi,

I am not sure if I understood correctly your question, but please check the below picture and the attached pbix file.

What I did was I create a measure like below, and put it into the filter pane and create a condition like below for the two visualizations.

Task Number measure: =
IF ( MAXX ( Task, RELATED ( Epic[Epic Number] ) ) = BLANK (), 0, 1 )

ranhunandan.pbix31 KB
- Anonymous
  Not applicable
  4 years ago
  Hi Kim,
  
  Thanks for the response. The issue is that when I select S2 in the second table, the visual shows all the values i.e. S1..S6 instead of S1..S3
  
  Raghu
  - bcdobbs
    Community Champion
    4 years ago
    I would use power query to merge all three tables into a single "task" dimension. Each row would be a task but also contain columns for story and epic. It's then much easier to build hierarchies.
    
    Currently you have a snowflake, while power bi likes star schema. Although it looks like you'd be duplicating lots of data, the way verticac works results in the model being small.
Thingsclump
Resolver V
4 years ago
Hi Anonymous

First of all I like the way you have explained the issue. Below solution worked for me. I usually avoid setting cross filter direction as both. but in your case, thats what is needed i think.

When E1 is selected.

When S2 is selected.

Don't forget to Mark this as a solution if i answered your question.

Thanks
Thingsclump
www.thingsclump.com
- Anonymous
  Not applicable
  4 years ago
  Hi,
  Thanks for the response. Please see the step which is not as per my expectation.
  Raghu
Icey
Community Support
4 years ago
Hi Anonymous ,

B.2 When I click on S2, then Task Number is filtered but both Story Number reverts to the full list and Epic Number also goes back to the original state!

What I want is that on clicking S2 is

Epic Number should still continue to highlight E1

Story Number should still show visual B.1

Task Number should show T4..T6

So basically selection of a row in the Story table seems to revert the filtering on the Epic table and the filtering earlier applied to itself (Story table).

In your scenario, when you filter on the Story Number, please hold down the "Ctrl" key.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  4 years ago
  Yes, that works, although it's not something I can expect end users of the report to do. Is there a better solution?
  
  Raghu
  - Icey
    Community Support
    4 years ago
    Hi Anonymous ,
    
    I'm afraid not.
    
    Could you let me know why do you want to use Table visual to filter data? Does "Slicer" not meet your requirements?
    
    Best Regards,
    
    Icey
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ritika_ritika
Regular Visitor
11 months ago
What if the two visuals are from the same table only, what would you suggest in that case for end users