topic Re: Implement data masking within matrix visual using DAX expressions in DAX Commands and Tips

Implement data masking within matrix visual using DAX expressions

smasl94 — Thu, 10 Apr 2025 08:58:01 GMT

Hi!

I am currently building a report and I would like to mask all leaf (i.e., age_group) values for a specific combination of association_id and gender if any of the leaf values is between 1 and 4 (but none of the column/row totals should be masked whatsoever).

In the first picture, you can see how the data looks like from the beginning.

I have constructed a DAX expression that looks like this:

```dax

num_members_masked_display =

VAR MemberCount =

SUM('gold fact_member_count_monthly'[num_members])

-- Check if this is a grand total row (no association in scope)

VAR IsGrandTotal =

NOT HASONEVALUE('gold fact_member_count_monthly'[association_id])

-- Check if this is a total column (no age group in scope)

VAR IsTotalColumn =

NOT ISINSCOPE('gold fact_member_count_monthly'[age_group])

-- Get current association and gender

VAR CurrentAssociation =

SELECTEDVALUE('gold fact_member_count_monthly'[association_id])

VAR CurrentGender =

SELECTEDVALUE('gold fact_member_count_monthly'[gender])

-- Should mask for this association + gender if ANY age group value is 1–4

VAR ShouldBeMaskedForAssociationGender =

CALCULATE(

COUNTROWS(

FILTER(

'gold fact_member_count_monthly',

VALUE('gold fact_member_count_monthly'[num_members]) >= 1 &&

VALUE('gold fact_member_count_monthly'[num_members]) <= 4

)

REMOVEFILTERS('gold fact_member_count_monthly'[age_group]),

KEEPFILTERS('gold fact_member_count_monthly'[association_id] = CurrentAssociation),

KEEPFILTERS('gold fact_member_count_monthly'[gender] = CurrentGender)

) > 0

RETURN

IF(

IsGrandTotal || IsTotalColumn,

MemberCount, -- Show totals

IF(

ShouldBeMaskedForAssociationGender,

"**",

MemberCount

)

```

Using the above DAX expression in a measurement and use that measurement instead of raw numbers seemed at first glance to work completely fine until I detected that there values that should not be masked but still get masked. It currently looks like this:

On the first row, you can see that the association with id "5C48E206-E97B-EF11-AC20-7C1E5220324F" gets all its leaf values for "Kvinna" masked which is correct as there is one leaf value for "Kvinna" that is in fact between 1 and 4, but when I look at "Man", then there is no value that is between 1 and 4 and yet all of them get masked. It works completely fine when there are leaf values between 1 and 4. However, for some rows, it seems to apply masking where it is not needed.

I sense that something very basic is missing in my DAX expression, but I have just not figured it out. Am I on a correct path with using measurements like this? Are there other alternatives that I may need to consider? Ideally, I would like to finish the DAX expression, but I am willing to listen to other approaches as I am not an experienced Power BI user.

Thanks for helping out in advance!

Re: Implement data masking within matrix visual using DAX expressions

bhanu_gautam — Thu, 10 Apr 2025 08:57:33 GMT

smasl94 , Try using

dax
num_members_masked_display =
VAR MemberCount =
SUM('gold fact_member_count_monthly_sylwester'[num_members])

-- Check if this is a grand total row (no association in scope)
VAR IsGrandTotal =
NOT HASONEVALUE('gold fact_member_count_monthly_sylwester'[association_id])

-- Check if this is a total column (no age group in scope)
VAR IsTotalColumn =
NOT ISINSCOPE('gold fact_member_count_monthly_sylwester'[age_group])

-- Get current association and gender
VAR CurrentAssociation =
SELECTEDVALUE('gold fact_member_count_monthly_sylwester'[association_id])
VAR CurrentGender =
SELECTEDVALUE('gold fact_member_count_monthly_sylwester'[gender])

-- Should mask for this association + gender if ANY age group value is 1–4
VAR ShouldBeMaskedForAssociationGender =
CALCULATE(
COUNTROWS(
FILTER(
'gold fact_member_count_monthly_sylwester',
'gold fact_member_count_monthly_sylwester'[num_members] >= 1 &&
'gold fact_member_count_monthly_sylwester'[num_members] <= 4 &&
'gold fact_member_count_monthly_sylwester'[association_id] = CurrentAssociation &&
'gold fact_member_count_monthly_sylwester'[gender] = CurrentGender
)
),
REMOVEFILTERS('gold fact_member_count_monthly_sylwester'[age_group])
) > 0

RETURN
IF(
IsGrandTotal || IsTotalColumn,
MemberCount, -- Show totals
IF(
ShouldBeMaskedForAssociationGender,
"**",
MemberCount
)
)

Re: Implement data masking within matrix visual using DAX expressions

smasl94 — Thu, 10 Apr 2025 09:01:39 GMT

Hi, bhanu_gautam!

Thank you very much for your suggestion!

I used that suggested DAX expression, but unfortunately, it seems like it persists. It is the same issue for e.g. the first row as mentioned originally in my post.

Re: Implement data masking within matrix visual using DAX expressions

v-sdhruv — Fri, 11 Apr 2025 12:37:23 GMT

Hi smasl94 ,
You can try modifying
VAR ShouldBeMaskedForAssociationGender with
VAR ShouldBeMaskedForAssociationGender=

FILTER(
ALL('gold fact_member_count_monthly'),
'gold fact_member_count_monthly'[association_id] = CurrentAssociation &&
'gold fact_member_count_monthly'[gender] = CurrentGender
)

VAR MaskCondition =
COUNTROWS(
FILTER(
MemberSubset,
VALUE('gold fact_member_count_monthly'[num_members]) >= 1 &&
VALUE('gold fact_member_count_monthly'[num_members]) <= 4
)
) > 0

RETURN
IF(
IsGrandTotal || IsTotalColumn,
MemberCount,
IF(
MaskCondition,
"**",
MemberCount
)
)
Additionallly,to further validate the measure, try to return rows with the given condition and check the root cause if this is actually returing the desired values with the correct gender.
Use:
RETURN
COUNTROWS(
FILTER(
VAR ShouldBeMaskedForAssociationGender,
VALUE('gold fact_member_count_monthly'[num_members]) >= 1 &&
VALUE('gold fact_member_count_monthly'[num_members]) <= 4
)
)

Hope this helps!

Re: Implement data masking within matrix visual using DAX expressions

v-sdhruv — Sun, 20 Apr 2025 12:04:25 GMT

Hi smasl94 ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

Re: Implement data masking within matrix visual using DAX expressions

smasl94 — Wed, 23 Apr 2025 21:45:19 GMT

Hi, all!

Thanks to all of you for providing solutions!

It turned out that there were some rows in the Azure SQL database table that contained null values for the age_group field, which messed up with the calculations. Therefore, I used bhanu_gautam's suggestion and then added the condition `NOT ISBLANK('gold fact_member_count_monthly_sylwester'[age_group])` within the FILTER function to exclude such rows from being accounted in the calculations, then it worked like a charm.

Once again, thank you for helping me out with this one!