Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have some data pertaining to staff, that shows:
Staff Member Name
Age Group
Directorate
Department
This is diosplayed on a report like this:
This is fine when there are a large number of staff in a dfepartment (in the above example there are 8 members of staff in HR); but when the sample size is too small (i.e. less than 5 members of staff in a department), instead of the table visual with the columns Age Group / Staff Count / Staff % I want to display a message along the lines of "Data Sample too small; the results can't be shown for data protection purposes".
I couldn't figure out a way to do this neatly (I tried altering the staff count measure but the best I could achieve was for a message to be displayed on multiple rows which wasn't what I wanted). What I'd like is to use perhaps a Card visual, or a text box) to display the message when the result is too low (I'm aware I may need a separate measure for this too, one that isn't filtered by "Age Group")
Here is my measure that I currently have to calculate the Staff Count:
Staff Count = COUNTROWS(Staff)
Does anybody know how I can achieve what I want? I looked into bookmarks but couldn't see how I could make this work.
Solved! Go to Solution.
@PetyrBaelish
You can do it using a few measures; the idea is to create a card visual and drop a measure that has the text you want to show.
Now adjust the fill color and font color then assign the conditional formatting accordingly. The only drawback in this approach is the visual is not interactable as the card visual sits on top of it.
In my sample, I set the message to show up if the count is below 50, click on the slicer, and check.
Download the file: https://1drv.ms/u/s!AmoScH5srsIYgYIG8CnFZje7RDJJfA?e=H5wEve
mCount =
COUNTROWS('Table')
mColorFill =
IF(
[mCount] > 50,
"#FFFFFF00",
"#FFFFAA"
)
mColorFont =
IF(
[mCount] > 50,
"#FFFFFF00",
"#000000"
)
mText =
IF(
[mCount] < 50,
"Data Sample too small; the results can't be shown for data protection purposes",
BLANK()
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@PetyrBaelish
You can do it using a few measures; the idea is to create a card visual and drop a measure that has the text you want to show.
Now adjust the fill color and font color then assign the conditional formatting accordingly. The only drawback in this approach is the visual is not interactable as the card visual sits on top of it.
In my sample, I set the message to show up if the count is below 50, click on the slicer, and check.
Download the file: https://1drv.ms/u/s!AmoScH5srsIYgYIG8CnFZje7RDJJfA?e=H5wEve
mCount =
COUNTROWS('Table')
mColorFill =
IF(
[mCount] > 50,
"#FFFFFF00",
"#FFFFAA"
)
mColorFont =
IF(
[mCount] > 50,
"#FFFFFF00",
"#000000"
)
mText =
IF(
[mCount] < 50,
"Data Sample too small; the results can't be shown for data protection purposes",
BLANK()
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
here is a possible solution for you:
https://exceleratorbi.com.au/show-or-hide-a-power-bi-visual-based-on-selection/
You will have to change it a little to fit your needs, but it is doable.
Cheers,
Sturla
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |