Implementing slicer selection on a table visual where the source tables are having inactive relation

[aanchalsharma18]

In my dashboard I am having 1 fact table called "Call" and 3 dimension tables namely, Doctors, Territory and Rel_Doctor_Territory in DIRECT QUERY mode.. 

As per the attached screenshot Doctors and Territory table and joined with Call table using active relationship. The REL_Doctors_Territory table is having common columns of both Doctors(REL_Doctors_Territory.Doctor_ID=Doctors.ID) and Territory(REL_Doctors_Territory.Territory_ID=Territory.ID) table but the REL table is having inactive relationship with Territory table to avoid circular dependency. I want to get data of doctors that have not been engaged and want to filter their data on the basis of Territory's Name. How can I get that done?

[Sahir_Maharaj]

Hello @aanchalsharma18,

Since the REL_Doctors_Territory / Territory link is inactive, you activate it dynamically inside measures. Can you please try this approach:

Doctors Not Engaged =
CALCULATE (
    DISTINCTCOUNT ( Doctors[ID] ),
    NOT ( Doctors[ID] IN VALUES ( Call[Doctor_ID] ) ),
    USERELATIONSHIP ( REL_Doctors_Territory[Territory_ID], Territory[ID] )
)

Also, if you need the actual list of doctors (not just counts), you can create a calculated table measure:

Unengaged Doctors By Territory =
CALCULATETABLE (
    EXCEPT (
        VALUES ( Doctors[ID] ),
        VALUES ( Call[Doctor_ID] )
    ),
    USERELATIONSHIP ( REL_Doctors_Territory[Territory_ID], Territory[ID] )
)

[aanchalsharma18]

Hi Sahir,

Thanks for the help, but I am still not getting the desired results. I missed one point

The Territory_Hierarchy is source of the Name slicer and it is joined with Territory table. I believe may be that is the reason the solution is not working for me.

Can you please help me with this

[johnt75]

Create a measure like

Doctor is not engaged =
VAR CalledDoctors =
    CALCULATETABLE ( DISTINCT ( Call[DoctorID] ), REMOVEFILTERS ( Territory ) )
VAR DoctorsInTerritory =
    CALCULATETABLE (
        VALUES ( Doctors[DoctorID] ),
        USERELATIONSHIP ( Territory[TerritoryID], REL_Doctors_Territory[TerritoryID] )
    )
VAR UnengagedDoctors =
    EXCEPT ( DoctorsInTerritory, CalledDoctors )
VAR Result =
    IF ( SELECTEDVALUE ( Doctors[DoctorID] ) IN UnengagedDoctors, 1 )
RETURN
    Result

Add this as a filter to a table or matrix containing Doctors[DoctorID] and set it to show only when the value is 1.

[aanchalsharma18]

Thanks for your help but I am still not able to filter the data on the basis of terrirtory name in my report.

[johnt75]

Can you share a PBIX with any confidential information removed or anonymised ? You can post a link to e.g. Google Drive, OneDrive etc.