Forum Discussion

Kay_Kalu's avatar
Kay_Kalu
Icon for Advocate I rankAdvocate I
10 hours ago

Cross 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. 

Need Help
power bi

2 Replies

  • ShahRukhSameer's avatar
    ShahRukhSameer
    Icon for Continued Contributor rankContinued Contributor
    9 hours ago

    Hi Kay_Kalu​,

    I think the issue is more with the data model than the DAX itself.

    The visuals on the left are based on TaskOccurrences, while the visuals on the right are based on Team Workload. The filter is flowing from TaskOccurrences to Team Workload, which is why selecting a bar on the left updates the visuals on the right. However, the reverse doesn't work because there isn't a valid filter path back to TaskOccurrences.

    I noticed you tried changing the MJ_Number relationship to Both directions, but Power BI throws an ambiguous path error. That usually happens when there are multiple routes available for filters to travel between the same tables, so Power BI doesn't know which path to use.

    Rather than enabling bi-directional filtering, I'd recommend creating a dedicated MJ_Number dimension table containing unique MJ numbers and connecting both TaskOccurrences and Team Workload to that table. Then use the dimension table in slicers and visuals that need to interact across both fact tables. That's generally the cleanest approach and avoids ambiguity issues.

    If changing the model isn't an option, you could also use TREATAS() in the measures for the Team Workload visuals so that selections from TaskOccurrences are applied virtually without changing relationships.

    Something like this:

    BAU Count =

    CALCULATE(

    COUNTROWS('Team Workload'),

    TREATAS(

    VALUES(TaskOccurrences[MJ_Number]),

    'Team Workload'[MJ_Number]))

    Based on the model screenshot, I'd personally go with the shared MJ_Number dimension table. It will make cross-filtering much more predictable and save you from relationship issues later on.

  • Prateekpal's avatar
    Prateekpal
    New Member
    7 hours ago

    Hi Kay_Kalu​ ,

    Based on given you can try checking out below :-

    1. Check your model and try to move more towards Star Schema.
    2. Avoid making cross joins and filtering both directions.
    3. If possible try connecting both the tables directly as i can see both tables have "MI Number" column as primary key.
    4. Check for visual interactions if they are off.