Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
NathanD
Regular Visitor

Dynamic Data Masking to anonymize data

Hello everyone, thank you for your attention,

 

I've been facing a big pain point on my report recently,

 

I'm trying to achieve something i would name like "Dynamic masking"

I'm on a matrix visual and wants to hide information via two criterias : 

-If the number is <5, display NA

-If there is only one row with <5 in the entire column. Assign the value NA to the second littlest value 

 

The objective is to not be able to find the number behind the value <5 by substracting the other values to the total.

The scenario : 
Having a matrix visual like this 

 Year 1
Versicolor10
Virginical7
Viridis4
Total21

 

I want the matrix visual to look like this 

 Year 1
Versicolor10
VirginicalNA
ViridisNA
Total21

 

I tried using this code but didn't achieve what i wanted to do 

Nombre de présents anonymisé =
VAR Total1 = SUM(eTauxReussite[txRssPresents])
VAR Total2 = SUM(eTauxReussite[txRssAdmis])
VAR Count_LessThan5 =
COUNTROWS(
FILTER(
ALLSELECTED(eTauxReussite),
Total1< 5
)
)
VAR IsSingleLessThan5 = Count_LessThan5 = 1
VAR RowRank =
RANKX(
ALLSELECTED(eTauxReussite),
Total1, , ASC
)
VAR ExtraMaskedRow = RowRank = 2
RETURN
IF(
Total1 < 5 &&Total1<>0 || (IsSingleLessThan5 && ExtraMaskedRow)|| Total2<5 && Total2<>0,
"NA",
Total1
)

 

Thank you in advance 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

I think you can use something like

Sum Value = SUM( 'Table'[Column2])

Show Value = 
IF(
	NOT ISINSCOPE('Table'[Column1]),
	FORMAT(
		SUM('Table'[Column2]),
		"#,#"
	),
	VAR CurrentTotal = [Sum Value]
	VAR Ranking = RANK(
		SKIP,
		ALLSELECTED('Table'[Column1]),
		ORDERBY(
			[Sum Value],
			ASC
		)
	)
	VAR Result = IF(
		Ranking <= 2 || CurrentTotal < 5,
		"N/A",
		FORMAT(
			CurrentTotal,
			"#,#"
		)
	)
	RETURN
		Result
)

Use the [Show Value] measure in your matrix and it will show values only when the value is >= 5 or the current row is not in the bottom 2 ranked by the value.

I think that the FORMAT functions are necessary because you can't have a measure returning multiple data types - e.g. whole number and string.

View solution in original post

2 REPLIES 2
johnt75
Super User
Super User

I think you can use something like

Sum Value = SUM( 'Table'[Column2])

Show Value = 
IF(
	NOT ISINSCOPE('Table'[Column1]),
	FORMAT(
		SUM('Table'[Column2]),
		"#,#"
	),
	VAR CurrentTotal = [Sum Value]
	VAR Ranking = RANK(
		SKIP,
		ALLSELECTED('Table'[Column1]),
		ORDERBY(
			[Sum Value],
			ASC
		)
	)
	VAR Result = IF(
		Ranking <= 2 || CurrentTotal < 5,
		"N/A",
		FORMAT(
			CurrentTotal,
			"#,#"
		)
	)
	RETURN
		Result
)

Use the [Show Value] measure in your matrix and it will show values only when the value is >= 5 or the current row is not in the bottom 2 ranked by the value.

I think that the FORMAT functions are necessary because you can't have a measure returning multiple data types - e.g. whole number and string.

Thanks a lot i think it answers the question, i think i just asked the question poorly so i'll do another post much more detailed. Thanks anyway for your time

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors