Forum Discussion
Advocate ICross Filter with visuals
In the image the section on the left is from table TaskOccurrences and the other visual are from table Team Workload. The problem I am trying to solve is looking at the image when you select a bar from the visuals on the left it filters the rest of the visuals but vise visa it wouldn't work see image below
for context of the build below is the relationship of the dashboard also the dax of the for right visuals count
when I try to make the team workload to Mi number relationship to cross filter both direction i get this error
the dax for right visual count below
I can't figure it out, if it possible I wouldn't mind a solution.
Hi Kay_Kalu ,
This problem is related with the question you identified that there are no relationships between both tables making the filtering not to work as you intend.
Based on your images is difficult to give you a best option since there are a complex model of relationships. However looking into your relationships you have the following:
Assigned Names (1) -> (Many) Team Workload
MI_Numbers (1) -> (Many) Team Workload
MI_Numbers (1) -> (Many) Task Ocurrences
You can have a couple of options to try for a workaround:
- Using Cross filter both direction using DAX
- If you use a CALCULATE and CROSSFILTER option for the filtering you can activate the bidirectional filtering
Value to be calculated = CALCULATE ( [Your Calculation], CROSSFILTER('Team Workload'[MI Number], 'MI Number'[MI Number], both))- A second option can be to create a measure to use in the filter pane of the visual that wouuld count the number of rows of the MI Number dimension this would create a context for the filter when you selected the values from another visual that uses that dimension
Filter Measure = COUNTROWS('Mi Number')Using this on the filter pane of the visualization and setting to is not blank may do the trick.
Once again it's difficult to pinpoint the correct option without checking the model because of it's complex relationships.
If you want you can share a mockup file and expected result, if the data is sensitive please send it trough private message.
Regards
Miguel Félix
- Using Cross filter both direction using DAX
2 Replies
- MFelix
Super User
Hi Kay_Kalu ,
This problem is related with the question you identified that there are no relationships between both tables making the filtering not to work as you intend.
Based on your images is difficult to give you a best option since there are a complex model of relationships. However looking into your relationships you have the following:
Assigned Names (1) -> (Many) Team Workload
MI_Numbers (1) -> (Many) Team Workload
MI_Numbers (1) -> (Many) Task Ocurrences
You can have a couple of options to try for a workaround:
- Using Cross filter both direction using DAX
- If you use a CALCULATE and CROSSFILTER option for the filtering you can activate the bidirectional filtering
Value to be calculated = CALCULATE ( [Your Calculation], CROSSFILTER('Team Workload'[MI Number], 'MI Number'[MI Number], both))- A second option can be to create a measure to use in the filter pane of the visual that wouuld count the number of rows of the MI Number dimension this would create a context for the filter when you selected the values from another visual that uses that dimension
Filter Measure = COUNTROWS('Mi Number')Using this on the filter pane of the visualization and setting to is not blank may do the trick.
Once again it's difficult to pinpoint the correct option without checking the model because of it's complex relationships.
If you want you can share a mockup file and expected result, if the data is sensitive please send it trough private message.
Regards
Miguel Félix
- Using Cross filter both direction using DAX
