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 ...
Need Help
  • 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This is definitely a step in the right direction! Thank you!!! Pasting below what I'm actually use, but I still have a problem. I can only get it to work when I filter the incidents table drastically with a page level filter. I already have the customer table filtered on 1 row for this testing purpose. Without the page level filter there are 2,300 rows being returned from the incidents table, and with it there are 26. 3 of which this measure shows '1' on which is perfect. It just doesn't work without this extreme filtering, even though it should only be comparing the 2300 rows from incidents and 1 row from customers.

         

        Measure 3 = 
    countrows(
        INTERSECT (
        VALUES( CUSTOMERS[ID] ),
        VALUES( INCIDENTS[CID] )
    )
)

        Also I had to change from account to ID since some of the accounts apparently had a 0 in front of them. 

         

        Just FYI I'm using direct query and there are a grand total of about 940000 customer records and about 4,000,000 incidents. This dashboard won't be useful showing that many records, but just for kicks and giggles I put the above measure on an unfiltered dash board that graphs counts(incidents[UniqueID]) and it returns abotu 743,000. So on a completely new unfiltered page it appears to work rolled up, but it won't work on my somewhat more narrowly filtered dashboard. So it's like it is trying to do the comparison on the 4M records before doing the other joins etc. I'm not sure what's up.