Forum Discussion

PaulBoden's avatar
PaulBoden
Icon for Helper I rankHelper I
4 years ago

Unexpected Cross-Filtering Results

Hi all,

 

Struggling with why one of my filters isn't working as expected - Would appreciate any help!

 

I have three tables:-

 

Table 1
Staff ID

Office Visits

Visit Date
Location
Table 2
Effective Hours (%)

Staff ID

Table 3
Staff ID

Work Category

 

Tables 1 and 3 are both joined to Table 2 with a many to one relationship on 'Staff_ID' and have both way cross filtering enabled.

 

Table one only records data about Staff who have visited the office, and I've used the following to create an additional column on Table 2, to show which members of staff have visited the office and which haven't:

 

WFO = IF(LOOKUPVALUE('Table1'[Staff_ID],'Table1'[Staff_ID],'Table2'[Staff_ID]) = BLANK(),
"Y",
"N"
)
 
This works well so far.
 
On my dashboard I have two donut charts showing 'Avg of Effective Hours (%)' broken down by 'Work Categories'. One showing data for staff who have worked in office (WFO filter = 'Y') and one for those who haven't (WFO filter = 'N'). 
 
This works as expected until I apply any kind of additional filter using Table one (i.e. by location or date). Whenever I try, my WFO colum only returns 'N' values and one of my donuts disappears.
 
 
Can anyone spot what I'm missing and advise around how to resolve? My expectation was that this would simply filter out the Staff and display those that meet the set conditions in the viz. 
 
Thanks in advance!
Need Help

3 Replies

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Paul,

     

    I think the logic on your WFO column is the wrong way round. Eg it returns "Y" for id's not found in table 1.

     

    Then because you have a bidirectional

    relationship and:

    "Table one only records data about Staff who have visited the office"


    The moment table 1 is included in filter context you only get staff who have worked in office (but are labelled wrongly).


    If you want to share a demo

    file can help rebuild.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi PaulBoden ,

     

    Can you provide simple data from three tables for testing? The output you expect can also be shown graphically. Looking forward to your reply.

     

    Best Regards,

    Community Support Team _Charlotte

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

  • PaulBoden's avatar
    PaulBoden
    Icon for Helper I rankHelper I
    4 years ago

    Thanks all for the replies (and bcdobbs for helping me identify the ordering mistake!).

     

    The data is sensitive so I haven't been able to share but when I have time over the weekend I'll recreate the scenario with mock data to share - I'd be very appreciative of any guidance so that I can avoid similar issues in future.