Forum Discussion
Filter without a Circular Reference
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
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.
Hi Anonymous,
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
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?
Hi Anonymous,
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