Implement data masking within matrix visual using DAX expressions

[smasl94]

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!

[smasl94 · Accepted solution]

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!

[v-sdhruv]

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

[v-sdhruv]

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!

[bhanu_gautam]

@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
)
)

[smasl94]

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.