Forum Discussion
Anonymized data
Hi,
I have an expensive table with each individual's rank. I would like to display an individual's data and their rank relative to all other (while anonymizing ths identites of the others).
I showing my data in excel as well as the expected output in PBIX, either as a table or as a graph.
choice 2choice 1Excel Data
Thank u in advance and have a good day everyone.
If your goal is to have the anonymized ranking table and the ±5 bar chart shown in your screenshots, driven by a single slicer on EXENUM. , you can try this solution step by step:
Step 1: Parameter (selected individual)
Create a "What-if" parameter or a disconnected table for the selected PS:
SelectedPS = SELECTEDVALUE('Table'[EXENUM])
Or use a slicer directly on EXENUM.
Step 2: Calculated column for display label
Identifiant Display =
VAR SelectedEXE = SELECTEDVALUE('Table'[EXENUM])
RETURN
IF(
'Table'[EXENUM] = SelectedEXE,
VALUE(FORMAT('Table'[EXENUM], "0")) & " - vous",
"PS anonymisé"
)
Step 3: Écart vs vous measure
Écart vs Vous =
VAR SelectedMNT =
CALCULATE(
MAX('Table'[TOT_MNT]),
FILTER(ALL('Table'), 'Table'[EXENUM] = SELECTEDVALUE('Table'[EXENUM]))
)
VAR CurrentMNT = MAX('Table'[TOT_MNT])
RETURN
IF(
MAX('Table'[EXENUM]) = SELECTEDVALUE('Table'[EXENUM]),
BLANK(),
CurrentMNT - SelectedMNT
)
Step 4: Filter to ±5 ranks (for the bar chart)
IsInScope_±5 =
VAR SelectedRank =
CALCULATE(
MAX('Table'[RG_MNT]),
FILTER(ALL('Table'), 'Table'[EXENUM] = SELECTEDVALUE('Table'[EXENUM]))
)
RETURN
ABS('Table'[RG_MNT] - SelectedRank) <= 5
Use this as a visual-level filter (IsInScope_±5 = TRUE) on both the table and the bar chart.
Step 5: Conditional formatting (gold color for "vous")
Create a measure for bar color:
Bar Color =
IF(
MAX('Table'[EXENUM]) = SELECTEDVALUE('Table'[EXENUM]),
"#F0B429", -- gold
"#3B6FC4" -- blue
)
Apply it in the bar chart → Columns → fx → Field value → Bar Color.
Table visual setup:
Column | Value
RANG | RG_MNT
IDENTIFIANT | Identifiant Display
TOT_MNT | TOT_MNT
ÉCART VS VOUS | Écart vs Vous
Finally, you sort by RG_MNT ascending. Add conditional formatting on Écart vs Vous: positive = green, negative = red.
4 Replies
Hi chock974
Please check this solved post Solved: Anonymizing certain data / Dynamic data masking - Microsoft Fabric Community by danextian. You should find your answer including a pbix sample file.Thank you for tagging me but with the relese of user-context aware calculated columns (still in preview), the approach I used have become oudated. User-context aware calculated columns makes masking data simpler and without too many workarounds.
Hi chock974, please try this - https://youtu.be/5xsaivC8mGM?si=hrfCqv_ppKgxgay2
You can achieve this with a DAX rank measure and anonymized labels. Show the selected person's details, replace other names with generic labels (e.g., Participant 1, Participant 2), and use a table or bar chart to display ranks while preserving privacy. 👍
If your goal is to have the anonymized ranking table and the ±5 bar chart shown in your screenshots, driven by a single slicer on EXENUM. , you can try this solution step by step:
Step 1: Parameter (selected individual)
Create a "What-if" parameter or a disconnected table for the selected PS:
SelectedPS = SELECTEDVALUE('Table'[EXENUM])
Or use a slicer directly on EXENUM.
Step 2: Calculated column for display label
Identifiant Display =
VAR SelectedEXE = SELECTEDVALUE('Table'[EXENUM])
RETURN
IF(
'Table'[EXENUM] = SelectedEXE,
VALUE(FORMAT('Table'[EXENUM], "0")) & " - vous",
"PS anonymisé"
)
Step 3: Écart vs vous measure
Écart vs Vous =
VAR SelectedMNT =
CALCULATE(
MAX('Table'[TOT_MNT]),
FILTER(ALL('Table'), 'Table'[EXENUM] = SELECTEDVALUE('Table'[EXENUM]))
)
VAR CurrentMNT = MAX('Table'[TOT_MNT])
RETURN
IF(
MAX('Table'[EXENUM]) = SELECTEDVALUE('Table'[EXENUM]),
BLANK(),
CurrentMNT - SelectedMNT
)
Step 4: Filter to ±5 ranks (for the bar chart)
IsInScope_±5 =
VAR SelectedRank =
CALCULATE(
MAX('Table'[RG_MNT]),
FILTER(ALL('Table'), 'Table'[EXENUM] = SELECTEDVALUE('Table'[EXENUM]))
)
RETURN
ABS('Table'[RG_MNT] - SelectedRank) <= 5
Use this as a visual-level filter (IsInScope_±5 = TRUE) on both the table and the bar chart.
Step 5: Conditional formatting (gold color for "vous")
Create a measure for bar color:
Bar Color =
IF(
MAX('Table'[EXENUM]) = SELECTEDVALUE('Table'[EXENUM]),
"#F0B429", -- gold
"#3B6FC4" -- blue
)
Apply it in the bar chart → Columns → fx → Field value → Bar Color.
Table visual setup:
Column | Value
RANG | RG_MNT
IDENTIFIANT | Identifiant Display
TOT_MNT | TOT_MNT
ÉCART VS VOUS | Écart vs Vous
Finally, you sort by RG_MNT ascending. Add conditional formatting on Écart vs Vous: positive = green, negative = red.
