Filter without a Circular Reference

[soldstatic]

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. 

[v-shex-msft]

Hi @soldstatic,

Current power bi not support to create dynamic calculate column/table based on filter/slicer, please use measure to instead.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

For circular reference error, you can take a look at following blog to know how to prevent this issue:

Avoiding circular dependency errors in DAX

Regards,

Xiaoxin Sheng

[soldstatic]

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.

[v-shex-msft]

Hi @soldstatic,

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

[soldstatic]

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.

[v-shex-msft]

Hi @soldstatic,

Yes, you can't direct use measure use source of slicer.
Maybe you can consider to convert this formula to calculate column to return flag, then you can use slicer to filter this column to apply filter effect on all related records.

CUSTOMERS Flag =
VAR _list =
    INTERSECT ( VALUES ( CUSTOMERS[ID] ), VALUES ( INCIDENTS[CID] ) )
RETURN
    IF ( CUSTOMERS[ID] IN _list, 1, 0 )

INCIDENTS Flag =
VAR _list =
    INTERSECT ( VALUES ( CUSTOMERS[ID] ), VALUES ( INCIDENTS[CID] ) )
RETURN
    IF ( INCIDENTS[CID] IN _list, 1, 0 )

Regards,

Xiaoxin Sheng

[soldstatic]

So I tried your suggestion below (Incidents flag version), and it returns an error saying that 'CONTAINSROW' is not allowed as part of calculated column DAX expressions on DirectQuery models. I'm not actually using containsrow, I'm using the formula exactly like you have it for the incidents flag. Any ideas?

[v-shex-msft]

Hi @soldstatic,

Sorry, I forgot to told your direct query has limitations to use dax functions in calculated column.

I haven't found some effective suggestion to deal with this, maybe you need to manually add measure to different level of filters to enable filter effect on your report.

Regards,

Xiaoxin Sheng