Building Dynamic Security for DirectQuery

Dynamic row-level security roles can also be implemented in DirectQuery models via a combination of user security tables and bidirectional cross-filtering relationships, such as department or region, to user-based security involving DAX measures and user permission tables which retrieve the report user’s identity and filter the data model based on this identity, respectively. DAX information functions, commonly used in the role security expressions of Import mode models, such as CONTAINS and LOOKUPVALUE, are not supported in DirectQuery mode models, thus requiring a relationship-based security design. Although limited to this single approach, dynamic security can be quickly developed for DirectQuery models and maintained easily, without the need for complex DAX security expressions.

This recipe walks through the steps and settings necessary to support dynamic security in a DirectQuery model.

Getting ready

To prepare for...