Masking with Measures and Calculation Groups

Posted On 2026-07-03

There are many ways to secure data before it even reaches Power BI, but there are also a few things you can do after the user have actual access to the reports too. Row Level security is one option and that works well for when users are not allowed to see parts of the data, like certain records. Other times, the data should be masked dynamically, for example you might be allowed to see information as long as more than 10 items are selected. Like if you’re working with credit card data, you should see the average spend but not be able to pin point a certain cards individual use.

I’m currently working on a M365 Usage report and the idea is not to pinpoint certain users, rather groups of people. When you collect the M365 Usage Data from the Graph API, it’ll look something like this:

Each record also have a date and either a Username or a scrambled pseudonymized ID depending on your org settings in the tenant. Now I want the report admins to be able to set a threshold for how many users that needs to be selected for a metric to show, so I first create a parameter named “Min_Group_Size” and give it a couple of values to select from.

The parameter is great because it’s so easy to change! A report admin could change this from the reporting view but also in the Power BI Services settings of the semantic model without even needing Power BI Desktop.

The downside is that I can’t fetch the parameter value in a measure by itself.

I can however generate a one-column table based on the parameter value, like this:

let
    Parameter = Min_Group_Size,
    #"Converted to Table" = #table(1, {{Parameter}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "MinGroupSize"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"MinGroupSize", Int64.Type}})
in
    #"Changed Type"

Now when creating my measures that will aggregate the usage data, I can use this simple structure. For each measure, I only need to change the name and row 2 that specifies what field to aggregate:

Now anytime the measure is evaluated, it will only show the actual metric if the number of users (in that context) is greater than the min Group size (currently 5).

Here’s the thing though. I want to to create a calculation group because I have many, many measures with this usage data just being summed, but for my KPI dashboard, I want to measure many more things based on this sum, like “total”, “sum in last 30 days”, “sum previously 30 days”, “trend, diff between those days in amount and %” and also metrics that have to do with users, such as number of users that have used this feature.

This will cause some problems but let’s build up to that. First, this is my calculation group with 10 items.

The selected measure above is for example looking like this:

For the user metrics I’m running a SUMX over the User table to determine if I should count the user as active or not. The measure above will calculate the actual value over 30 days while the bottom one will calculate number of distinct users that have made more than 1 action in the period.

This will not work though. If you try, you’ll just get an error message. The reason is logical though. In the measure, we say that we will only show the actual value as long as the min group size is met, i.e. 5 users in this case. But in the SUMX loop, we will only have 1 user available at a time, meaning the “Selectedmeasure” will always be evaluated to “—“.

To solve this, I’ve made a measure that will check if any of the Calculation group items are currently selected. This measure will be true when evaluated inside of my calculation group and otherwise always false, like if you just place the measure on a visual.

Now on all my measures, I added this rule, so that I will actually still get the metric even for smaller groups of users, but only if it’s inside the calculation group.

Now in my calculation Items, I add the rule back in so if the number of users that is active is less than the min group size, I still mask it:

When you activate Calculation Groups, it automatically sets the model to “discourage implicit measures”.

This means you can no longer drag a field straight from the table onto a visual to aggregate it, you need to use a measure. This is actually recommended when using masking even if you don’t use calculation groups. See with this, you can be sure that the report developers can’t accidentally present data unmasked on any dashboard.

Cheers!