The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 | |
Versicolor | 10 |
Virginical | 7 |
Viridis | 4 |
Total | 21 |
I want the matrix visual to look like this
Year 1 | |
Versicolor | 10 |
Virginical | NA |
Viridis | NA |
Total | 21 |
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
Solved! Go to Solution.
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.
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
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |