Using CustomData to make row filters dynamic

DAX contains a function called CustomData, the sole purpose of which seems to be to make a connection string dynamic.  The example below shows how to create a role which can be configurable to show the data for any MAM region.

Creating the security role

To create a dynamic row filter to solve the problem above, first create a role as follows:

If the custom data flag is not passed in, this will return False; otherwise, it will return True only if the region for the current row equals the one passed in via the connection string.

If you're wanting to copy this formula, here it is:

Setting the custom data flag in the connection string

If you now create a pivot table using this role, you'll see no data:

If you use this role when viewing a pivot table based upon your model, you'll see nothing!

This is because the custom data flag hasn't been set, and so the row filter formula for the region always returns False.  To get round this, amend the connection string:

One way to edit the connection string used is to choose this ribbon option.

Choose to show the Definition tab in the dialog box which appears:

Click on this tab first.

Add a value for the CustomData flag:

You don't have to make this the last attribute, but it's easiest to read if you do.

When you select OK, your pivot table will show data for the London region:

For transactions occurring in the London region, the row filter formula now returns True.