Tag: power bi visuals

Introduction: Attribute Control Chart

Monitor process quality, facilitating timely anomaly detection and continuous improvement

Introduction

The Attribute Control Chart in Power BI offers a comprehensive view of process quality by allowing users to monitor defects/defectives and their corresponding metrics.

The schema will help you to select the most appropriate Attribute Control Chart.

This visual supports six types of Attribute Control Charts:

  • u chart: Tracks the number of defects per unit when the sample size varies.
  • Laney u’ chart: A modified version of the u chart to adjust for overdispersion or underdispersion in your data.
  • c chart: Monitors the number of defects in each sample when the sample size is constant.
  • p chart: Observes the proportion of defective items in each sample when the sample size varies.
  • Laney p’ chart: A modified version of the p chart to adjust for overdispersion or underdispersion in your data.
  • np chart: Displays the count of defective items in each sample when the sample size is constant.

Key features of the Attribute Control Chart are:

  • Dynamic Data Analysis: Users can explore and analyze data trends by filtering and slicing data based on various attributes such as time periods, product categories, or production lines.
  • Statistical Control Limits: The visual dynamically calculates and displays control limits, allowing users to quickly identify variations in the process and distinguish between common cause and special cause variations.
  • Automatic signal detection: The visual automatically detects significant signals in your data, allowing users to focus on signals and ignore random noise.
  • Format the objects: All lines, markers and axes can be formatted independently, and support theme settings.
  • Selection & Highlighting: Like any standard Power BI Chart you can make use of the Selection & Highlighting functions within the Attribute Control Chart.
  • Full Tooltip support: Besides the default Tooltip behaviour (show the value of the element you hover) you can also add additional fields to the tooltip.
  • Full Bookmark support: Like any of the standard visuals the Attribute Control Chart supports Bookmarks.
  • Drill-Down Capability: Users can drill down into specific data points to investigate underlying factors contributing to quality issues or improvements.
  • Real-Time Monitoring: Provides real -time updates as new data is ingested, enabling users to stay informed about process performance and take timely corrective actions.

Some of the benefits of using Attribute Control Charts are: improve process visibility, early detection of anomalies and enhanced collaboration.

How to use: Build visual

To use the Attribute Control Chart, you need to have a dataset with at least a Lot, Sample and Defect / Defectives. The available fields are:

  • Lot: here you add the category you want to use as your x-axis. It refers to a group or batch of items. This can also be a date.
  • Sample: this field contains the number of items randomly selected from a lot for inspections or testing.
  • Defect / Defectives: are the characteristics or conditions that do not conform to the specified requirements or standards. Defects refer to individual nonconforming units, while defectives refer to the units containing one or more defects.
  • Target: this field contains your target points, which can be shown as a line or a sequence of markers. Multiple targets are supported, you can add up to three categories.
  • Central line: the Central Line will be calculated automatically. You can also add your own Central Line by including it in this field.
  • Control limits: the Control Limits (or natural process limits) are calculated automatically, but can be added here as well. You need to add two categories to this bucket: one for the lower limit and one for the upper limit. The order doesn’t matter. Adding the limits will overrule any automatic calculation.
  • Signals: an indicator will be shown in the chart as soon as this field is non-empty.
  • Tooltips: the fields added here will show up in the tooltip when the user hovers a specific data point.

How to use: Format visual

The Attribute Control Chart comes with many of the standard Power BI formatting options. Titles, axis labels, line and markers appearance, etc. It’s all there.

  • License: here you can enter your PREMIUM license information to activate the visual with a Premium license. These fields are not used for the PER USER license.
  • Legend: show/hide the legend by clicking the toggle on/off. Available subcategories:
    • Position: here you can set the position of the legend to Top, Bottom, Left, Right, Top Center, Bottom Center, Left Center, Right Center.
    • Title: switch on/off the legend title and add a title to the Legend Name.
    • Style: change the Font family, text Size, emphasized text (Bold, Italic, Underline) and Color of the legend.
  • X-axis: the X-axis are the horizontal values and title of the X-axis. 
    • Style: by default the Axis type is set to Categorical. If the axis is based on dates the Axis type is set Continuous. In the Date type option you can define the formatting of the date labels on the X-axis. The options range from Year to Seconds. Note that making changes to this option does not invoke any changing to the grouping of your data.
    • Values: format here the Font, Size, emphasized text (Bold, Italic, Underline) and Color of the X-axis labels.
    • Title: here you can rename and format the Title text, Font, Size, emphasized text (Bold, Italic, Underline), Title position (Align left, Align center, Align right) and Title color.
  • Y-axis: the Y-axis are the vertical values and title of the Y-axis.
    • Range: here you can set a Start/ End value.
    • Values: format here the Font, Size, emphasized text (Bold, Italic, Underline), Color, Display units and Value decimal places of the Y-axis labels.
    • Title: here you can rename and format the Title text, Style, Font, Size, emphasized text (Bold, Italic, Underline), Title position (Align left, Align center, Align right) and Title color.
  • Gridlines: by default the chart has horizontal thin and light background lines in the grid.
    • Horizontal : turn on/off the horizontal gridlines and set the Style (Dashed, Solid, Dotted), the Color and Width.
  • Data Colors: set the colors for the Defect rate, and (if enabled) the markers on that line. In the Defect rate (outside Control limits), for highlighting purposes, you can change the color of the Defect rate markers when they fall outside the Control Limits bandwidth. The Defect rate line itself will not change color when outside the control limits. You can also change the color for the Central line, Control limits, Signals and Target(s).
  • Data point: contains all options to format the lines and markers of the chart such as the Defect rate line width (minimum line width can be set to 0 to show data points only), the CL line width and the Target line width. You can enable the Show defect rate markers (by default off), Show target Line (by default off) and Show target markers. Change the Marker size. Enable the Show 1 sigma and 2 sigma-lines option. Enable the Show as CL as step when you want to show the Central Line stepped including the Upper and Lower control limits. By default the Show target markers and Show control limits area options are on.
  • Data labels: enable the UCL, CL and LCL values as a final value label at the end of the line(s). Available options:
    • Overflow text: enable this option to show overflowed text.
    • Show background: set on to change the Background color and Background opacity.
    • Style: change the Font family, text Size, emphasized text (Bold, Italic, Underline) and Color of the UCL, CL and LCL data labels.
  • Calculation: See Calculation card for more information.
    • Attribute chart type: p-Chart (default), c-Chart, np-Chart, Laney p-Chart, u-Chart, Laney u-Chart.
    • Allow rule override and Allow export data enable these options to override rules and export data in Focus mode (Statistics per Rule must be On). To export data make sure the setting Allow downloads from custom visuals is enabled in the Admin Portal.
    • Subgroup size: default value = 1
    • Rules: switch off/ on specific rules and change the n-Value. The minimum n-Value allowed is 3.
    • Names: when you use automatic calculation you can change the names of the LCL, CL and UCL in the legend and tooltip.
  • Zoomslider: enable the zoomslider option to easily examine a smaller range of the date in the Attribute Control Chart without having to use a filter. The zoomslider is added on the chart’s x-axis if a Date is mapped in the Lot. It can be used for horizontal scrolling. Click and drag endpoints on sliders to adjust the dimensions of the chart, slide both endpoints toward the center. The closer the two endpoints are to each other, the more you zoom in to display shorter, finer segments. Click and hold down the left mouse button on the center section of the zoom bar and then scroll left or right to a particular point. You can also click on the label to display and select from the Calendar instead. The zoomslider can also be switched off/on. Available formatting subcategories:
    • Color: to change the color of the zoomslider (endpoints and line).
    • Style: to change the Font family, text Size, emphasized text (Bold, Italic, Underline) and Font color of the zoomslider date labels.
  • Statistics per Rule: enable this option and then click in the visual either the Focus mode icon or the ellipsis called More options and select Show as a table to see a table with all statistics about the applied rules. This table is particularly helpful if you would like to be able to know which rules are breaking on a recurring basis on your control charts. If Allow rule override and/ or Allow export data are on in the Calculation card, the options will be also available to the user.
    • Format the Title: switch on/ off the show title option and change the Title Font, Size, emphasized text (Bold, Italic, Underline) and Color.

The General settings contain options that affect the visual container and are consistent across all visual types. Here you can also customize the general Title and Tooltips.

Calculation card

Here you will find the options that affect your analysis the most. You can change the number of points considered for the average and each rule.

Available options:

  • Attribute chart type: p-Chart (default), c-Chart, np-Chart, Laney p-Chart, u-Chart, Laney u-Chart.
  • Allow rule override: enable this option and the Statistics per Rule to allow users to switch on and off certain rules. In Focus mode, select the Change Rules icon to get a menu with the rules and n-values. These settings will not be stored/ persisted.
  • Allow export data: enable this option and the Statistics per Rule to allow users to download data in Focus mode. The setting Allow downloads from custom visuals must be enabled in the Admin Portal. For more information see Tutorial: Exporting and Downloading Calculated Data from the Attribute Control Chart.
  • Show signals: When one of the rules is violated, then a small indicator will be shown in the chart. In case you have add a field to the Signal bucket, then non-empty values will also result in an indicator. Hover over the indicator to see which rule has been violated.
  • Subgroup size: this option allows you to group the data points for rendering and calculation. It is only be visible in case the Central Line and Control Limits wells are empty. The data points in the dataset will be grouped into a new data point and only the grouped data points will be rendered in the chart. Default value = 1

Rules

  • Rule 1: One point is more than 3 standard deviations from the center line.
  • Rule 2: Nine (or more) points in a row are on the same side of the center line.
  • Rule 3: Six (or more) points in a row are all increasing or all decreasing.
  • Rule 4: Fourteen (or more) points in a row alternate in direction, increasing then decreasing.

Considerations

By default a p-Chart will show up when adding a category to the Lot, Sample and Defect / Defectives wells, go to Format visual > Calculation > Attribute chart type, click to get a drop down menu and select another chart type.

If the calculation of the LCL results in a negative number, it will be replaced with 0. If you add your own LCL category with negative value, the negative value will show up in the labels and tooltips.

Using p-Chart and Laney p’-Chart for Yield: if you prefer seeing charts in terms of yields (ratio of non-defective parts) instead of defectives (ratio of defective parts), set the non-defective parts category in the Defect / Defectives field, in this case the Lower Control Limit does not go below 0 and the Upper Control Limit will not exceed 1.

u-Chart and c-Chart

The u-Chart (Unit Chart) and c-Chart (Count Chart) are quality control charts used to monitor count-type data, both are used to track defects.

The u-Chart controls the average number of defects per unit while the c-Chart controls the total number of defects per unit. A c-Chart can only be used if the sample size remains constant. If the sample sizes aren’t constant, use the u-Chart instead.

Use a u-Chart to monitor: defects in manufactured products, customer returns, damaged goods, medication errors, defects in software, failed test cases, inventory discrepancies, defects in building materials.

Use a c-Chart to monitor: number of defective units per batch, number of customer complaints per day, number of damaged items per shipment, number of medication errors per week, number of software defects per release, number of billing errors per billing cycle.

p-Chart and np-Chart

The p-Chart (Proportion Chart) and np-Chart (Nr of Defectives per Unit Chart) are control charts used to monitor proportions both are used to track defectives.

The p-Chart controls the proportion of items and is used for ‘pass/fail’ inspection, while the np-Chart controls the number items.

Use a p-Chart to monitor: proportion of defective items, proportion of customer complaints, proportion of damaged packages, proportion of medication errors, proportion of software modules with defects in a release, proportion of building materials failing quality.

Use a np-Chart to monitor: number of scratches per unit inspected, number of customer complaints per day, number of damaged packages per delivery, number of medication errors per day, number of bugs found per software module.

Laney u’-Chart and Laney p’-Chart

The Laney u’-Chart and Laney p’-Chart are attribute control charts used to monitor large sample sizes (>5000) and the data shows overdispersion or underdispersion. This can lead to false indications of out-of-control points on a traditional u- or p-Charts.

Use a Laney u’-Chart to monitor: tracking the number of medication errors per patient, the number of falls per patient, or the number of post-surgical complications per procedure; tracking the number of complaints or escalations per customer interaction, the number of errors per customer order, or the number of service failures per service call.

Use a Laney p’-Chart to monitor: the proportion of hospital-acquired infections over time, with each data point representing a weekly or monthly period; the proportion of defective units produced in a manufacturing process over time, with each data point representing a batch.

For any questions or remarks about this Visual, please contact us by email at Nova Silva Support or visit the community forum.

Attribute Chart calculations

Each of the charts supported by the Attribute Chart has their own specific calculations. This article shows and illustrates the applied formulas.

c chart calculations

np chart calculations

p chart calculations

Laney p’ chart calculations

u chart calculations

Laney u’ chart calculations

Tutorial: Exporting and Downloading Calculated Data from the Attribute Control Chart

Users can export data from the Attribute Control Chart into a file for further analysis or sharing purposes. Exporting and downloading data is an easy process and offers flexibility in choosing the format and destination for the exported data. In this brief guide, we’ll walk you through the essential steps.

Try it yourself!

Here’s what you need to export and download calculated data from the Attribute Control Chart:
– Enable the setting Allow downloads from custom visuals in the Admin Portal
– a Power BI report with your Dataset
– Build and Format the Attribute Control Chart
– Save and Share

Enable Allow downloads from custom visuals in the Admin Portal

This setting is disabled by default and applies to all visuals including those managed by your organizational store, Desktop, and web. When this setting is enabled, users can download data from a custom visual into a file on their storage device. 

To manage Power BI visuals you must be a Global administrator or have been assigned the Fabric administrator role. Go to the Admin portal > Tenant settings, scroll down to Power BI visuals and enable Allow downloads from custom visuals. When this setting is enabled, users can download data from the Attribute Control Chart into a file. It can export to files of the following types: .csv, .txt, .json, .tmplt, .xml, .pdf and .xlxs

  1. Expand the Allow downloads from custom visuals settings.
  2. Select Enabled.
  3. Choose who can download files:
    • Select The entire organization option to allow everyone in your organization to download data from a visual into a file.
    • Select the Specific security groups option to limit downloading files to specific security groups. Enter the security groups you want in the Enter security groups text bar. The security groups you specify are included by default. If you want to exclude these security groups and include everyone else in the organization, select the Except specific security groups option.
  4. Select Apply.

The UI tenant settings only affect the Power BI service. If you want these settings to take effect in Power BI Desktop, use AD Group Policy.

KeyValue nameValue
Software\Policies\Microsoft\Power BI Desktop\AllowCVToExportDataToFile0 – Disable
1 – Enable (Default)

When AllowCVToExportDataToFile is set to 1, the custom visual can export data to a file only if:

  • The feature switch in the admin portal is enabled.
  • The user is logged on.

Enable Allow export data in the Attribute Control Chart

  1. Open your Power BI report and add a Attribute Control Chart.
  2. Go to Format visual and enable Statistics per Rule.
  3. Expand the Calculation card and enable Allow export data.
  4. In the chart click Focus mode icon.
  5. Click the Download Data icon.
  6. The window Download from a custom visual? appears asking to confirm that the visual is from a trusted source.
  7. Click Download button.
  8. Rename and Save file.
  9. Open the file in Excel.
  10. Power BI exports the data to a (.csv) file. By default, your export is saved to your local Downloads folder.

Considerations and limitations

  • Allow downloads from custom visuals setting must be enabled by your Power BI administrator.
  • The API is supported only in the Power BI service and Power BI desktop.
  • The size limit for a downloaded file is 30 MB.

Overdispersion and underdispersion

Overdispersion and underdispersion are concepts used in statistical analysis to describe the variability of data compared to what would be expected under a particular probability distribution.

Overdispersion

Overdispersion occurs when the observed variability in the data is greater than what would be expected based on a theoretical probability distribution. In other words, the data exhibit more variation than predicted by the assumed distribution. This could happen, for example, if there are additional sources of variation or if the data are more spread out than the distribution assumes. In the context of count data, overdispersion might occur when the variance of the data exceeds the mean, which is not consistent with a Poisson or binomial distribution.

Underdispersion

Conversely, underdispersion occurs when the observed variability in the data is less than what would be expected based on the assumed distribution. In this case, the data exhibit less variation than predicted by the theoretical distribution. Underdispersion might occur if there are factors that constrain variability or if the data are more clustered or concentrated than expected.

Laney charts

Additionally, in the context of attribute control charts, Laney charts provide a means to account for overdispersion or underdispersion. For instance, a Laney P’ Chart or a Laney U’ Chart can better distinguish between common-cause and special-cause variations compared to traditional attributes charts like a P Chart or a U Chart when overdispersion or underdispersion is present. These Laney charts incorporate adjustments, such as Sigma Z, to accommodate for overdispersion or underdispersion, ensuring more accurate control limits for process monitoring.

Tutorial: Exporting and Downloading Calculated Data

Users can export data from the Control Chart XmR for Power BI into a file for further analysis or sharing. In this brief guide, we’ll walk you through the essential steps.

Try it yourself!

Here’s what you need to export and download calculated data from the Control Chart XmR:
– Enable the setting Allow downloads from custom visuals in the Admin Portal
– a Power BI report with your Dataset
– Build and Format the Control Chart XmR
– Save and Share

Enable Allow downloads from custom visuals in the Admin Portal

To manage Power BI visuals you must be a Global administrator or have been assigned the Fabric administrator role. Go to the Admin portal > Tenant settings, scroll down to Power BI visuals and enable Allow downloads from custom visuals. When this setting is enabled, users can download data from the Control Chart XmR into a file. It can export to files of the following types: .csv, .txt, .json, .tmplt, .xml, .pdf and .xlxs

  1. Expand the Allow downloads from custom visuals settings.
  2. Select Enabled.
  3. Choose who can download files:
    • Select The entire organization option to allow everyone in your organization to download data from a visual into a file.
    • Select the Specific security groups option to limit downloading files to specific security groups. Enter the security groups you want in the Enter security groups text bar. The security groups you specify are included by default. If you want to exclude these security groups and include everyone else in the organization, select the Except specific security groups option.
  4. Select Apply.

The UI tenant settings only affect the Power BI service. If you want these settings to take effect in Power BI Desktop, use AD Group Policy.

KeyValue nameValue
Software\Policies\Microsoft\Power BI Desktop\AllowCVToExportDataToFile0 – Disable
1 – Enable (Default)

When AllowCVToExportDataToFile is set to 1, the custom visual can export data to a file only if:

  • The feature switch in the admin portal is enabled.
  • The user is logged on.

Enable Allow export data in the Control XmR Chart

  1. Open your Power BI report and add a Control Chart XmR.
  2. Go to Format visual and enable Statistics per Rule.
  3. Expand the Calculation card and enable Allow export data.
  4. In the chart click Focus mode icon.
  5. Click the Download Data icon.
  6. The window Download from a custom visual? appears asking to confirm that the visual is from a trusted source.
  7. Click Download button.
  8. Rename and Save file.
  9. Open the file in Excel.
  10. Power BI exports the data to a (.csv) file. By default, your export is saved to your local Downloads folder.

Considerations and limitations

  • Allow downloads from custom visuals setting must be enabled by your Power BI administrator.
  • The API is supported only in the Power BI service and Power BI desktop.
  • The size limit for a downloaded file is 30 MB.

Tutorial: Setting up a Custom date scale

Adding a Custom date scale to your PowerGantt Chart

Now every user of the PowerGantt Chart can also define and include their own custom scale date. Adding a custom scale date to your PowerGantt Chart allows you to visualize project timelines and tasks more effectively. A custom scale date provides greater flexibility and precision in representing project schedules because there are unlimited number of custom date/time scales, like: fiscal years, special weeknumbers, AM/PM split of the day, etc..

In this article, you will find step-by-step instructions on how to achieve this.

Try it yourself!

Here’s what you need to define and set up a Custom scale date:
– a Power BI report with your Dataset (including task names, start dates, end dates, etc. )
– a Dataset with a date or time table
– Link tables
– Build and Format the PowerGantt Chart
– Save and Share

1. Create a Date Table

A custom date table is essential for implementing a custom date scale. This table will include all the dates you want to display on the Power Gantt Chart. You can generate a date table with DAX or Power Query.

Your date table should have: a date column, unique values, no BLANKs and no missing dates.

1.1 Using DAX

To work with DAX, you must have at least one date table in your model. A date table is a table that meets the following requirements:

  • It must have a column of data type date (or date/time).
  • The date column must contain unique values (dates should appear once).
  • The date column must not contain BLANKs (needs to have valid dates).
  • The date column must not have any missing dates.
  • The date column must span full years. A year isn’t necessarily a calendar year (January-December).
  • The date table must be marked as a date table.
  1. Click the Table view icon from the left menu or the Modeling tab from the top menu
  2. Click New Table
  3. Rename your new table
  4. Enter the DAX expressions to create a date table and press Enter
  5. Optionally, select the Column and update the Format
  6. In the Data pane, right-click on it and Mark as date table.

You have generated a date table with DAX, now you need to build a relationship between your dataset and the date table. Go to section 2. Link Tables

1.2 Using Power Query editor

  1. Click Table view icon from the left menu
  2. Click Get Data and select Blank query from the drop-down menu
  3. Select Advanced Editor
  4. Enter the Power Query M functions to create a date table and click Done
  5. Rename query
  6. Close & Apply.

If you add only a calendar table, convert it To Table in the Convert menu on the ribbon. Click OK in the next menu (no selection needed). After converting the list to a table, rename the column to “Date” and right-click and select Change Type > Date.

If your organization has already a global date table in the source data, you can use Power Query to connect to the date table.

You have generated a date table with Power Query, now you need to build a relationship between your dataset and the date table.

2. Link Tables

Connect the newly created date table with your existing dataset based on the date fields. You need to establish a relationship between the date table and the date fields in your dataset.

  1. Click Model view from the left menu
  2. Click on the Manage relationships from the top menu
  3. Click New…
  4. From the first drop-down, select your Dataset table and select a column with dates (for example the Start date)
  5. On the next drop-down, select the Date table you just created and select the Date column
  6. By default Cardinality is set to Many to one (*:1)
  7. By default the Cross filter direction is Single
  8. Press OK and Close buttons.

Your date table is now linked to your dataset.

3. Build visual

Add a PowerGantt Chart to your report and use the Custom scale date.

  1. In Power BI, open or locate the report with the PowerGantt visual
  2. Add categories to the fields (Item, Parent, Item Name, Start date, End date,…)
  3. Add the Date category to the Custom scale date
  4. Add categories to the Custom scale groups
  5. Right-click on each category in the Custom scale date and Custom scale group fields, make sure to click/select Don’t summarize and Show items with no data.

4. Format visual

Take advantage of the additional formatting features available for the Date scale in the formatting pane and customize it according to your preferences.

Go to Format visual pane and expand the Date scale card to:

  • Enable or disable the Date scale.
  • Show only groups in custom scale option.
  • Enable Overflow text.
  • Set a Start and End Date Range.
  • Style your date scale by setting the Font family, text Size and emphasized text (Bold, Italic, Underline).
  • Enable or disable the Today line and change the Style (solid, dotted, dashed), Color and Width of the line.

Additionally, change the Color and Background color of the Date scale under the Column headers card.

Save your Power BI report and publish it to the Power BI service.

Additional information

For good design practices to create date tables in your data models when using Power BI Desktop, see Create date tables in Power BI Desktop in the Microsoft Learn platform.

There are several reference materials and guides available for DAX and M Query, that can help you to define your custom date.

Data Analysis Expressions (DAX) Reference: https://learn.microsoft.com/en-us/dax/

DAX Guide: https://dax.guide/

CALENDAR DAX Function

Date = CALENDAR (DATE (2024, 1, 1), DATE (2024, 12, 31))

DAX Sample used in screenshots

DateTable = 
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(YEAR(TODAY()), 12, 31) 
RETURN
    ADDCOLUMNS (
        CALENDAR (StartDate, EndDate),
	"Year", YEAR([Date]),
        "QtrYr","Q" & FORMAT([Date], "Q YY"),
    	"Quarternumber", QUARTER([Date]),
    	"Month", FORMAT([Date], "MMMM"),
    	"Monthnumber", MONTH([Date]),
    	"MonthShortName", FORMAT([Date], "MMM"),
    	"Weekday", FORMAT([Date], "dddd"),
    	"Weeknumber", WEEKNUM([Date],1),
    	"WeekdayShortName", FORMAT([Date], "ddd"),
    	"DayOfWeek", WEEKDAY([Date]),
    	"WeekdayStartMonday", WEEKDAY([Date],2)
    )

Power Query M formula language: https://learn.microsoft.com/en-us/powerquery-m/

Power Query M formula language: https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

M Query Samples

= List.Dates(#date(2024,1,1),365,#duration(1,0,0,0))

let
    Source = List.Dates(#date(2024,1,1),365,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Year", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"

Nova Silva Power BI Certified visuals: Ensuring Security and Privacy

In this article we will explore how using Power BI Certified visuals help organizations to protect data integrity and ensure the security and privacy of sensitive information.

What are Power BI Certified visuals?

Certified Power BI visuals are visuals in AppSource that meet certain specified code requirements that the Microsoft Power BI team has tested and approved. The tests are designed to check that the visual doesn’t access external services or resources, and that it follows secure coding patterns and guidelines. All certified visuals must pass all the certification requirements.

Why are Nova Silva visuals Power BI Certified?

All our Power BI visuals (including the Shielded HTML Viewer) are certified by Microsoft. At Nova Silva we develop visuals based on the Microsoft Certification requirements, so our visuals are tested to verify that they don’t access external services or resources, and that they follow secure coding patterns and guidelines. An important requirement to pass the Microsoft certification tests is that the visuals cannot connect with any external server or service. In other words: our visuals do not share any of your data with anyone, not even with us. This is the main reason why we certify all our visuals: we want to make sure you are the only party that can access your data.

When you use our visuals, you do not make use of any Nova Silva infrastructure or platform. Our visuals do not share data with any external service or resource, we have no access to any of your data, nor do we play a role in any production services. We publish our visuals (and any updates) to Microsoft. Microsoft will check our code to determine if it complies with their quality and security standards. After passing these certification tests, Microsoft will publish the visual to the Microsoft AppSource. This is where you will download and use it from. The next image gives an overview of how we work with Microsoft on our Power BI visuals.

The code of our visuals (at run time) are on a Microsoft managed environment. The only way we can change this code is through the Microsoft Certification process. Our visuals interact with Power BI through a Visuals API. This API provides the visual with the right data and context. The visual only renders the result on the screen within Power BI.

Microsoft Certification Process

This is the process to certify our visuals:

  • Comply with Microsoft’s Certification requirements.
  • Submit the Power BI visual for certification: visual is published via the Partner Center.
  • Certification Test: every time a new version of a certified visual is submitted to the marketplace, the visual’s version update goes under the same certification checks. The version update certification is automatic. If the update is rejected because of a violation, an email is sent to the developer explaining what needs to be fixed.
  • Publication timeline: the process of deploying to AppSource may take some time. The Power BI visual will be available to download from AppSource when this process is complete.

Each time we develop or update a visual we perform our own internal testing to make sure we comply with all Microsoft certification requirements. Then we publish the source code to Microsoft, and they perform their Certification Test. After passing all tests Microsoft will publish the visual to the Microsoft AppSource. This is the environment where our customers can access the visual and use it within their Power BI Environment.

Each time we publish a (new) version of our visual it first needs to pass the Microsoft Certification tests. This includes a thorough review of our source code by Microsoft engineers. A certified visual cannot lose its certification with a new update. Instead, the update is rejected. If the update is rejected because of a violation, an email is sent to Nova Silva explaining what needs to be fixed.

What are the requirements to get Power BI Certified?

These are some of the requirements to get a Power BI visual certified.

List of requirements:

  • Share the source code with Microsoft via private repository (with two-factor authentication)
  • Use the latest API version
  • Use ESLint with the Microsoft required security rules to scan errors and security violations
  • Use latest version of powerbi-visuals-tools to write the Power BI visual
  • 1200 Power BI visuals additional certification policies
  • Use only public reviewable OSS components
  • Support Rendering Events API
  • DOM is manipulated safely
  • Visual is approved for publishing in the AppSource
  • Do not pass or transmit customer data externally
  • Not accessing external services or resources by the visual

Before submitting a Power BI visual for certification:

To learn more about the certification process, go to Microsoft.

What tests are done during the certification process?

The certification process tests include, but aren’t limited to:

  • Code reviews
  • Static code analysis
  • Data leakage
  • Data fuzzing
  • Penetration testing
  • Access XSS testing
  • Malicious data injection
  • Input validation
  • Functional testing

What is not allowed?

  • Accessing external services or resources. For example, no HTTP/S or WebSocket requests can go out of Power BI to any services. Therefore, WebAccess privileges should be empty, or omitted, in the capabilities settings.
  • Using innerHTML, or D3.html (user data or user input).
  • JavaScript errors or exceptions in the browser console, for any input data.
  • Arbitrary or dynamic code such as eval(), unsafe use of settimeout(), requestAnimationFrame(), setinterval(user input function), and user input or user data.
  • Minified JavaScript files or projects.

How to identify Power BI Certified visuals in AppSource?

Certified visuals will be clearly identified by:

  • Filtering with Sort by: Power BI Certified to display only certified Power BI visuals in the AppSource.
  • With a small badge (yellow or blue checkmark) on the visual’s card, both in AppSource or when importing from within Power BI interface.
  • When the Power BI visual card is clicked in AppSource, a PBI Certified yellow badge and the message under Visual capabilities will indicate that This visual is certified by Power BI.

Non-certified visuals: some Power BI visuals aren’t certified because they don’t comply with one or more of the certification requirements. For example, a map Power BI visual connecting to an external service, or a Power BI visual using commercial libraries can’t be certified.

When that is the case the visual has no badge and will show this information:

Allow access to certified Power BI visuals only

As a Power BI/ Fabric administrator for your organization, you can control the type of Power BI visuals that users can access across the organization and limit the actions users can perform.

To manage Power BI visuals, you must be a Power BI/ Fabric administrator. When this setting is enabled, only certified Power BI visuals render in your organization’s reports and dashboards. Power BI visuals from AppSource or files that aren’t certified return an error message. This setting is disabled by default and doesn’t apply to visuals in your organizational store.

  • From the admin portal, select Add and use certified visuals only.
  • Select Enabled.
  • Select Apply.

UI changes to tenant settings apply only to the Power BI service. To manage the certified visuals tenant setting in Power BI Desktop, use AD Group Policy.

KeyValue nameValue
Software\Policies\Microsoft\Power BI Desktop\EnableUncertifiedVisuals0 – Disable
1 – Enable (Default)

Did you know?

Microsoft certified our first Power BI visual, the Shielded HTML Viewer, back in 2020! It was a long process and took several code reviews by Microsoft before we got our first visual certified.
Sometimes, users report the Shielded HTML Viewer does not work with LinkedIn or YouTube, our visuals do not render anything from any external server or service as this is one of the main requirements to be certified. This can only be achieved with a non-certified visual.
Currently all Nova Silva visuals are Power BI Certified, this guarantees our visuals are safe and secure and ensures they meet the highest data security standards.
We are committed to maintain those standards, all our visuals are either certified or in the process of getting certified.
Power BI certified visuals offer more features than non-certified visuals, like:
– Export to PowerPoint
– Export to PDF
– or display the visual in received emails when a user subscribes to report pages.

Tutorial: Purchasing, Assigning and Managing PER USER Licenses from Microsoft AppSource

Introduction

The PER USER license is sold directly through the Microsoft AppSource. These licenses can be purchased monthly/yearly. It supports the payment options you have with Microsoft for any Azure related purchases. For most organizations this is limited to credit card payments.

Every user, that is both Creator and Viewer need a valid license to use the visual. For example, if you have 2 users creating reports with the visual and 10 users who only consume the reports, you need: 2+10=12 user licenses. After you purchase these licenses from the Microsoft AppSource you need to assign the licenses to each user or user group. This is done at the Microsoft Admin Center.

Before purchasing a PER USER license through the Microsoft AppSource please read the Considerations below!

Considerations

The PER USER licenses can only be purchased directly by the organization that will use the visual and not via distributor/reseller.
There are currently no options to re-assign these licenses between organizations.

The best is that the tenant admin does the purchase (through the Microsoft AppSource).
If the tenant admin owns the licenses (Org licenses), then any of the license admins (Global Admin, License Admin or User Admin) can manage and assign the licenses.

Currently, the following Power BI environments don’t support PER USER license management or license enforcement:
• Embedded – Publish To Web (public URL) , PaaS embed
• National clouds (Depends on general support for transactability in national clouds)
• RS Server (No planned support)
• Exporting (PDF\PPT) using REST API
For unsupported environments you need a PREMIUM license.

The Nova Silva visuals you can download from the AppSource are Certified Power BI visuals that meet certain specified code requirements that the Microsoft Power BI team has tested and approved. The tests are designed to check that the visual doesn’t access external services or resources. Our visuals are updated automatically when a new version is available.

Purchasing PER USER Licenses

To purchase a Nova Silva visual through the Microsoft AppSource, click Buy now and complete the Checkout path. See below:

  1. Go to the Microsoft AppSource
  2. Click on the Nova Silva visual you want to purchase
  3. Optionally click the Plans + Pricing tab for an overview of the available plans and prices
  4. Click the Buy now button
  5. Checkout – Plan
    • Select a plan: Personal, Team, Departmental, Organizational or Corporate
    • Click Next button
  6. Checkout – Price + billing
    • Set Billing term to 1-month or 1-year subscription
    • Set Recurring billing: by default auto-renewal is On
    • Provide Number of users (both Creators and Viewers are considered Users)
    • Up next, choose your payment method: (you can pay with a credit card, prepaid cards are not supported)
    • Click Next button
  7. Checkout – Payment
  8. Review order and if applicable add Tax ID
    • Click Accept agreement and place order button to complete purchase.

You can add a purchase number to the billing profile of your order. At the final stage of your checkout, select Add new under the billing profile and add the Purchase order number in the field.

After you complete your purchase, you can find your receipt in the Microsoft 365 admin center by clicking Download invoice under Bills & payments > Invoices.

Billing and Global admins can also pay by invoice if that’s set up for your company. Read more about setting payment methods. Follow these steps to see your billing profile.

Assigning PER USER Licenses

After the purchase through Microsoft AppSource is complete and successful, to activate the licenses the  buyer (owner) will need to assign the licenses to the users.

If the tenant admin owns the licenses (Org licenses), then any of the license admins (Global Admin, License Admin or User Admin) can manage the licenses.
However, if the subscription owner is non-admin, he will have access to the portal and only he can manage the subscription and licenses. The tenant admin can see all subscriptions purchased under the tenant account, including the subscriptions purchased by non-admins. You can assign licenses to users in the same tenant that you purchased subscription under regardless of the location.

  1. Go to admin.microsoft.com and select the Licenses link under the Billing node from the menu (a list of visuals appears).
  2. Select the Nova Silva visual you want to assign. If it isn’t visible, make sure no filters are currently applied that may be hiding it.
  3. In the next page, click + Assign licenses.
  4. Search for the user by name or email address.
  5. Select the user(s) you want to assign the licenses to.
  6. Click Assign button.

Buyers get a license assigned to them automatically. It can take up to five minutes from the time of purchase for the license to take effect. If you want to add more users or update your subscription, go to the admin center. You can assign the license to the users directly by providing name or email, or through Microsoft Groups either Mail-enabled or Security Group.

If the owner of the subscription leaves the company, tenant admin purchases (typically known as organizational purchases) continue to work as-is. If the owner was an admin, any license admin (Global Admin, License Admin, User Admin) can continue to manage the licenses. The subscriptions can be managed by any Billing Admin or Global Admin with access to the billing account used for purchase.
If the owner wasn’t an admin, the license will continue to work as long as the subscription is active. However, the subscription can’t be managed by anyone else.

After you assign a license in the Microsoft 365 Admin Center, the license should be available within a few minutes.

If you were just assigned a license, refresh the report to activate it by doing one of the following:

  • If you’re using the licensed visual in the Power BI Service, refresh the report by hitting F5.
  • If you’re using the licensed visual in the Power BI Desktop, close and reopen it.

If, after you refresh, the license is still not available, wait a while and try again. It could take up to 30 minutes. If it takes longer than 24 hours, please contact Microsoft’s support team.

Managing PER USER Licenses

Access the information about the licenses you purchased and manage it in Your Products under the Billing node in the Microsoft 365 admin center.

Check the visual to activate the options or click the icons (Buy licenses or More actions). Here you can Buy licenses, Remove licenses, View billing profile, Edit recurring billing (to turn on/ off recurring bill) and Cancel subscription.

As a billing or global admin, you can see all subscriptions in the Microsoft 365 admin center, and you can cancel them.

In the admin center, go to Billing > Your products
Find and check the visual you want to manage
Click Cancel subscription
You’re eligible for a full refund if you cancel your subscription within seven days of purchase. Refunds aren’t available for subscriptions canceled after that time period. PER USER license purchases and refunds are handled by Microsoft and not by Nova Silva.

For help managing licenses, contact support or click the Help & Support option on the bottom right section of your screen. If you raise a support ticket provide the Subscription ID.

Please note that any changes you make may take up to one hour to process.

Considerations and limitations

Currently, the following Power BI environments don’t support PER USER license management or license enforcement:

  • Publish to web (P2W)
  • Sovereign or government clouds
  • RS (report server) – no Microsoft Entra ID
  • PaaS Power BI embedded App owns data
  • Exporting (PDF\PPT) using REST API

For unsupported environments you need a PREMIUM license, this can be purchased from our webshop.