Forum Discussion
Implement data masking within matrix visual using DAX expressions
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!
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
)
)
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.