Forum Discussion

aanchalsharma18's avatar
aanchalsharma18
Frequent Visitor
1 year ago

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

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...
Need Help
Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
1 year ago

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's avatar
    aanchalsharma18
    Frequent Visitor
    1 year ago

    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