Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Filter without a Circular Reference

I have three tables, jobs, customers and incidents. Jobs is the primary source of info and represents a single instance of 'work' that someone needs to do. A job can impact multiple customers and is related through an intermediate table (i won't get into that here). Incidents spawn jobs, like a request which could come from an anonymous source. It could also come from a customer. Incidents are related to jobs by the job ID. I'm attempting to build a dashboard that would be able to show some of these relationships. I have successfully linked customers <-> jobs <-> incidents, but there is no way to link customers <-> incidents because that would create a circular reference. Technically incidents can also link to customers via the account_number, and if I unlinked incidents from jobs I could use a many to many link on account_number.

 

So currently I have a dashboard that shows the customers which can be filtered down, for instance to a single customer. Simply dropping a table of jobs on the dashboard, this is already filtered now to any job that impacted that customer since I have the relationship in the model and it is set to crossfilter both ways. When I drop the incidents table on here, I see all incidents related to all of those jobs regardless of source. My goal is to be able to have this specific dashboard ALSO filter the incidents table on account numbers that are currently selected in the customer table. 

 

Is there a way to build a measure or column in incidents that would spit out something I could use as a filter? IE in incidents I could maybe use a calculated column like:

 

IncidentFiler = if(incidents[account_num] in allselected(customers[account_number]),1,0)

Or is there another way to do this I'm not thinking of? I looked at list.contains but I'm having trouble making it work. 

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Xiaoxin, thank you for your reply!

       

      The problem with the dependency is in the data model. I understand that this relationship would be ambiguous in the data model, but it is a legitimate relationship for this case. I can leave this relationship off, or I can make it but leave it inactive which is fine. But this doesn't allow me to filter like I need to.

       

      I've tried a couple things, specifically a measure on the incidents table many different ways. If I could get it to return a flag based on whether or not the customer number appears in the customer table as currectly selected in the visuals, I could use that as the filter. So here's two of the attempts I've taken stabs at:

       

      Measure = calculate(
    if(CUSTOMERS[ACCOUNT_NUMBER] = incidents[account_num],1,0)
    ,CROSSFILTER(CUSTOMERS[ACCOUNT_NUMBER],INCIDENTS[ACCOUNT_NUM],both))

       

      Measure 2 = if(incidents[Account_Num] in values(ces_customers_a[account_number]),1,0)

      I'm just banging my head on a wall here. Any additional advice would be much appreciated.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous,

         

        I'm not so clear for your requirement. Do you mean use current table records find out account numbers which existed in correspond related tables ?

         

        If this is a case, you can try to use INTERSECT function to extract column which existed in two tables.

        Measure =
VAR _list =
    INTERSECT (
        VALUES ( CUSTOMERS[ACCOUNT_NUMBER] ),
        VALUES ( incidents[account_num] )
    )
RETURN
    IF (
        SELECTEDVALUE ( incidents[Account_Num] ) IN _list
            || SELECTEDVALUE ( CUSTOMERS[ACCOUNT_NUMBER] ) IN _list,
        1,
        0
    )

        If above not help, please share a pbix file with some same data and expected result for test an coding formula.

         

        Regards,

        Xiaoxin Sheng