Hi all,
In my report, I have multiple visuals with different measures and logic. Most of them are based on the count of users. I have a requirement not to show the visual if n <30 for example: if the number of users is less than 30 not to perform the calculation.
Is there a way to show a message that Not enough data available when the n < 30 for a visual? that may be applied as a page filter or something?
Thanks in advance!
Totally doable in Power BI, using a couple of cool tricks to control transparency of visuals via DAX and conditional formatting. Here’s a basic mockup of your scenario that I created:
The trick here is to create your visuals as normal, and then create a card that says “Insufficient Sample Size” or something to that effect. You can then use the transparency attribute (00) of hex codes to make the background and font of the overlaying card transparent or not, depending on whether or not your sample size is greater than the designated sample size threshold for display.
In this case, I set the sample size for display threshold using a what-if parameter. I actually synced this to a slider on the front page, so that you can control the visuals on all pages using this front-page setting (as long as you sync each page to that page 1 slider).
Here are the two key measures you need to control the transparency of the background and the font for the overlaying card:
CF Background =
IF(
[Sample Size] >= [Sample Size Threshold Value],
"#FFFFFF00",
"#FFFFFF"
)
CF Font =
IF(
[Sample Size] >= [Sample Size Threshold Value],
"#FFFFFF00",
"Black"
)
I hope this is helpful. Full solution file attached.
Hi iasma, a sample size is typically counted against a specific filter and context. I believe one may generate a calculated column in the lookup, for example if it’s Product, Device or Facility, to count up the sample size of your interest. This column can then be used in guiding calculation of particular measure whether or not even calculate anything by adjusting the context based on whether or not sample size n < 30 of that specific object in the lookup.
Hi @iasma, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thank you @BrainJ
This is such an easy trick. However, I wasn’t able to apply it to my dataset.
The sample size threshold is a bit confusing for me. The probabilities of running into the sample size is when end users choose narrow time frame or choose a company that has limited number of users. So what will determine the minimum sample size message is dropdown of (company) and a slicer of (date). Let say a logic will be whenever the countrows of table is <30 then we get minimum sample size message “Insufficient Sample Size”
I am trying to add sample of my data but my data model is so big it was taking so much time to trim the data :’(
Please advise me if this solution can be modified .
Sure - solution is easily modifiable. In this case, you’d just delete the what-if parameter and add a simple measure:
Sample Size Threshold Value = 30
Sample size is already determined by COUNTROWS, so you should be good to go with this simple change. The only thing you’d need to make sure to do is to synch your date slicer to every page of your report where you’d want this “Insufficient Data” visual to trigger, even if you don’t show the slicer on the page, in order that it’s properly filtering your COUNTROWS() statement.
I just used the what-if parameter here to make it interactive so that you could see the status of the visual change as you moved the slider, but you can set the threshold by parameter, by slicer, or by measure/constant as we’re doing above.
I hope that’s clear. Please let me know if you have any questions.
I appreciate you taking the time @BrainJ.
So I deleted the parameter and added the measure for Sample Size Threshold Value.
The No Display measure is not transparent when the sample size >30
Not sure what I am missing here.
For the “Insufficient Sample Size” card, have you gone into Conditional Formatting and set the background and font colors to their respective measure values as follows?:
Great – glad you got what you needed. Fun problem – I enjoyed working with you on this.
– Brian
You are the best!
One thing is that if this overlaying card is on a visual, we can’t select anything in the visual to filter data and interact with other data, right?
I’m convinced that with the power afforded by bookmarks, page navigation, conditional actions, conditional formatting, etc. that almost anything in Power BI is possible from a UX/navigation standpoint.
Two requests:
since this is a new scenario, and the current one is marked as “solved” can you please start this one as a new thread, since some folks looking to provide solutions only look at open threads, and we want to encourage their input as well; and
can you please provide some detail as to a specific situation/mockup/example of what you want to do along these lines?
Thanks!
– Brian
