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 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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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