Funnel Plot Limitation of at least 10 Data Points

[Anonymous]

I am new to Power BI & would like to create a Funnel Plot for medical outcomes.  I know that I can Import an Excel file that contains my X- axis Population variable (Total Surgeries) and Y-axis Occurrence variable (Ratio of Revisions) along with Region which is the Tooltip & the actual data points.  I successfully Import the Funnel Plot Custom Visual from the Marketplace & Enabled Script Visuals.  But after I drag these variables into the appropriate boxes under Visualizations, I get a statistical error that my data does not contain at least 10 data points.  I only have maximum of 8 Regions.  Does anyone know how to override this limitation?

(SAS allows the same funnelplots using Proc SGPlot for less than 10 points)

[dm-p]

Hi @Anonymous,

I've reviewed the souce code for the Funnel Plot visual and the minimum limit of 10 is set in there. If this is something you would like the MS Custom Visuals team to address (as they maintain it), you can either create an idea in the community, or raise an issue in their GitHub repo and hopefully they can address.

The alternative would be to take the source code and build your own version with a lower limit.

Regards,

Daniel

[Anonymous]

Daniel,

Please be very specific in your reply.  What are our options if we need this plot minimum to be changed to 0? 

My department does reporting for region-wide Kaiser Permenente so this is of great importance.  We need to create funnel plots Regionally for our 4 to 8 Regions but this 10 plot minumum restriction is preventing these reports.

What files can we change?  In which folder, etc?

Who is a contact who can make these changes for us, if we cannot?

Thanks!

Craig

[dm-p]

Hi @Anonymous,

Ideally the path in my post above - request the changes with MS - would be the "correct" way to handle it.

As the visual is MIT-licensed it is open source and provided "as-is" & has no specific warranty, so you are able to make the changes yourself if Microsoft are not keen to add this feature to the visual. If this is an option for you, then you'll need to do the following:

1. Install the Power BI Visuals SDK.
   • There are some prerequisites, which are detailed in the linked guide - the referenced section - Setting up the developer environment will be all you need for this. 
   • Because the SDK has moved on a bit and the visual uses an older version, I would suggest changing the command in step 2 of installing packages to:
     

     npm i -g powerbi-visuals-tools@1.13

2. Clone or download the visual repository to your developer environment.
   • Run npm i in the root folder of the checkout to ensure that all dependencies are loaded.
   • If you want to test/debug using the developer visual in the Power BI Service rather than packaging and loading into Desktop each time you want to test a change then you'll need to do the bits in this section of the above documentation to ensure that end-to-end connectivity is okay.
3. Modify the appropriate files:
   • script.r: Change the minPoints variable value in line 139 (as linked in my last post) to 0.
   • pbiviz.json: You will need to change the guid on line 5 to be different from the original. If a visual is available in the Marketplace and you import a modified version and the guid is the same as this, then the one in the Marketplace will always be applied.
   • Given the above, you may also wish to edit the displayName property on line 4 so that when you import the visual, you can tell the difference between the Marketplace one and yours if they are both in your report.
4. Package and test:
   • Run pbiviz package in the root folder of your checkout. This will package and place a .pbiviz file in the dist folder.
   • Import this file into your report and change any visuals you have to use this rather than the Marketplace version.

It may be worth bearing in mind that lowering the limit might have unintended side-effects on how the author (MS) designed the visual to work and you would need to do some extensive testing and verification to be sure that it fits your intended use. There might be additional changes to make beyond this that would require someone with R expertise to work through (R is not my particular area of expertise... I know enough to be dangerous 😉 ).

If you cannot make these changes yourself, I can do a one-off package of this with the revisions I detailed above and make available as a .pbiviz. Similarly it would be provided "as-is" with no specific warranty - so if there are knock-on effects - I cannot guarantee I would have the capacity or bandwidth to support them.

Hopefully this might give you some ideas on how you could proceed.

Regards,

Daniel

[Anonymous]

Thanks Daniel,

My department does not have a license for only has license for 'Visuals SDK'.  We also do not know how long Microsoft will take to make this change.  We need to create these funnel plot reports within the next few months.

So could you create a one-off Visual package for me?

As I mentioned, the Minimum Data point needs to be '2' or '3' or '4' since one of our reports only has 4 Regions.  Our Statistician explained that this report is still Ok since we have thousands of Surgeries in our baseline data (the X-axis)

I was able to create a funnel plot by Facility (we have more than 10).

But I have a other questions/issues if you can address:

1) How can I see the values for the y-axis Ratio for each plot?

Also, how can I see what PBI calculates as the Upper & Lower Confidence Limits?  I specifically want to know what the 2nd CL is that I set at 99.5%.

2) These values are needed because we want to change the color of the circle to RED if the plot (Facility) falls Above the Upper 99.5% CL, and to GREEN if the plot falls Below the Lower 95.5% CL.  Can you tell me if & how to do this?

3) Do you know if & how to change the SHAPE from circle to Triangel or Diamond just for ONE plot (Facility)? 

Thanks!

Craig

[dm-p]

Just to fill you in, you don't need a license for the SDK - it's completely free and I've been developing visuals on a Power BI Free license for a while before moving to Pro.

Here's a link to the revised .pbiviz file with the restriction reduced to 2. This will show as Funnel plot (customised) in your report, e.g.:

I've tested using the sample workbook provided for the visual and the original one will error if there are less than 10 categories. This revised one will only error if there's less than 2 (i.e. 1 or 0).

Regarding your other questions, you'd need someone with R knowledge to review the custom visual code and verify. I'm okay with the framework and TypeScript for developing custom visuals, but I really am not confident enough to advise on aspects or elements of the R language, and to my eyes, the code is pretty complicated beyond the variable limits identified up-top.

Sorry I can't help with these particular aspects. I would suggest posting in the Custom Visuals Development Discussion forums and the MS Custom Visuals team (who regularly reply to posts in there) might be able to adivse further.

Regards,

Daniel

[Anonymous]

Daniel,

Thanks alot for creating the revised Viz file that I successfully used to create my plots with only 4 to 8 data points.

I posted here last week but have not heard back from anyone.  Could you help direct my questions to the appropriate people?

https://community.powerbi.com/t5/Custom-Visuals-Development/Help-Changing-Funnel-Plot-Colors-and-Sha...