Data Entry Matrix Setup and Usage

This topic covers the fundamental aspects of creating a write-back capable report using the Data Entry Matrix (DEMx) visual.

Applying Priorities

Coloring Priority / Applying Protocol

1. Highlight Colors
2. Goal Seek Font / Background Color
3. Modified Font / Background Color
4. Context Menu » Cell Formatting » coloring
5. Coloring from Context menu » Select (based on [General] » Selected Item Font / Background color)
6. Coloring from [Formatting Measure]
7. [Conditional Formatting]
8. Disabled Font / Background Color
9. Primary / Secondary Font / Background color

Format String Priority / Applying Protocol

1. Context Menu » Cell Formatting » Format string on content cells

2. [General] » Format string

3. Format string from database

Setup and Configuration of the Visual

This main section describes the available configuration options for the Data Entry Matrix. Also, the following pages describe short step-by-step instructions for building a simple report using DEMx. You will find the details of the configuration elements later in this document.

Validate the Write-back Service

After installation you might want to be sure that the Write-Back Service is installed properly. You can validate that by navigating to your deployed URL, which is in the following format:

http(s)://yourserverName/PPWebService/PPWebService.svc

If you see the following page after the page load, then the Write-Back Service is up and running.

Setting up the Basics

After you launch Power BI Desktop, connected to a data source, and imported the visuals (VPService, Data Entry Matrix) into your report, the very first step is the configuration of VPService visual.

We must tell the visuals where they can find the Write-Back Service and which connection they should use during the process. You can see detailed information in this chapter how to configure VPService.

The configuration options will appear in the Format section of the Visualizations after you drop the first column into the Data Entry Matrix visual. You must complete the following steps to have a working report element for the write-back functionality.

Starting with [Data Entry], you must set the Web Service, Type and Connection properties (see referring chapters) to make the Write-Back Service working.

Note! The content of [Data Entry] » Web Service property has to be exactly the same (case sensitive too!), like in VPService ([Web Service] » Url).

Finally, please make sure you use the matrix properly, meaning adding measure(s) to the Values and multiple dimensions to Rows and Columns. A table supports two dimensions; however, a matrix makes it easier to display data across multiple dimensions. Dimensions are usually text or date(time) type of values, the measures are numeric values.

If you add a numeric type of column to the Rows/ Columns, you will not be able to modify and save the changes in the Data Entry Matrix.

Add Dimensions and Measures

Simply add some fields into the Rows and Columns sections of the visual and add a write-back compatible measure.

If you want to add some slicers, please refer to the Importance of SmartFilter Helper visual chapter.

Applying Style

Navigate to the [Column Headers] and [Row Headers] configuration and set a desired font size and color. You can create different themes for the totals and subtotals if you go to the [Totals] property group. For alternating colors, go the [Values] property group and set different background colors for the primary and secondary parameters. You might produce a result below.

Publishing and Testing the Report

When you finish your report, publish it to either your On-premises Power BI Report Server or to PowerBI.com. The write-back will work only when your report is published, so you cannot test the full functionality in Power BI Desktop while you are authoring it. After publishing your report try to modify a cell value and click on save changes. Your modification should be visible in your report.

Completing the above steps, you should have a working, simple, write-back capable report using Data Entry Matrix visual.

The following pages cover more detailed configuration options as well as typical use-cases that you might find useful for your needs.

Configuration

This chapter contains the configurations regarding the connectivity with the Write-Back Service.

WebService

• Manually enter the Write-Back Service URL, which should point to the hosting machine or app service where it was installed. The typical URL format is: http(s)://WEBSERVICE_COMPUTER_NAME/PPWebservice/PPWebservice.svc. Ensure there is no extra slash at the end of the URL.
• Use the fx button to extract the string from a measure or column value. This way, any updates to the web service URL and connection name only need to be made in one place.

Type

This option refers to the connection type that the Write-Back Service will use. The setting is required.

• SSAS: Set the connection string directly in the Connection property (leave the connection blank if using the default "SSASConnection" in set in the web.config of the Webservice)
• SSAS Datasource: Set a connection name in the Connection property defined in the web.config of the Webservice.

Connection

Specify Name of the SQL or SSAS connection defined in the web.config file of the webservice, or the connection string directly, or leave blank depending on the Type setting.

Note: Maximum length of this property is 250 characters.

Data Entry (Settings)

This section contains some basic behavior of the DEMx visual.

Customer

Customer name provided by us along with the License server license key. If already specified in the web.config file (generally, you do not have to set this as it is done by the setup), leave it blank.

Domain

Set it to one of the following. This should be left blank as it is configured in the Write-Back Service web.config file during setup.

• the fixed the name of the internal domain used (e.g.: DOMAIN)
• auto: use the domain part (part after @) of the powerbi.com user
• auto-short: use the short domain part (part after @ and before the .) of the powerbi.com user
• azure: use the full powerbi.com username (e.g.: user@domain.com) for Azure AD authentication
• leave empty for no domain (e.g.: for a SQL authentication user)
• SQL=…,SSAS=… set domain options for SQL and SSAS separately
• Custom user mapping of powerbi.com users to SQL and SSAS users can be set up in the UserMapping table in the SQL database (should contain User, SQLUser, SSASUser varchar columns) For more information please visit: https://support.poweronbi.com/portal/kb/articles/custom-user-mapping-when-using-powerbi-com-service

Enable Drill Through

If you enable this setting, by double clicking on a cell a popup will be rendered in which the related records of the fact table will be shown. Those rows will be displayed that are in the intersection of the dimensions determined by the cell. For example, if you clicked on the cell of January for Apple 0.5l bottle product, an SQL query will be executed against the underlying database with a WHERE predicate containing the mentioned dimension members, so the result will be those rows from the Sales fact table that belong to January and the Apple product. Note that this query will be executed on the SQL database even if you have an in-memory SSAS tabular model.

Input Culture

The input culture depends on the value selected in the Region field which allows you to either auto-detect the browser language or manually set the preferred language. The separator used in the number, date, and date-time columns is based on this selection.

Read Only

If it is turned ON, the matrix becomes read-only, so end users will not be able to change values and commit write-back.

Apply Write Security Measures To Totals

Off: When a cell becomes read-only due to a write security measure, corresponding totals and subtotals will become read-only as well.

On: Totals and subtotals become read-only if the write security measure can be evaluated on these levels and its value indicates they should be read-only.

Windows Authentication

If you are in an On-premises environment using Power BI Report Server, this setting must be turned ON so that the Windows credentials can be passed back to the underlying data source. If you are completely in the cloud - your reports are published to PowerBI.com service - and you are using Azure AD, the setting should be turned OFF so that AD credentials will be used when accessing the data source.

In the case of Gateway turning ON Windows Authentication will have the following effects: Instead of setting the PowerBI.com credentials in the Write-Back Service request, the visual posts the windows login context. This value (e.g.: domain\user instead of username@domain.com) will be set if you use USERNAME() in computed and/or default value columns (see later in this document) as well in SQL context variables. Also, this makes it possible to use impersonation, and it is necessary to be turned on if Windows Authentication is the required option set for authentication in IIS for the Write-Back Service.

Save Complete Message

If this setting is enabled, the user gets a message after saving if it is successfully done.

Prevent Auto Refresh

This setting blocks grid updates when you have unsaved changes. It prompts you to save or discard your edits, preventing unintentional loss. Turn on this setting to create a block period for automatic refresh.

General (Settings)

Under general settings you can control the behavior of the visual as well as how it is rendered.

Visual Version

This is a way to check what version of the DEMx visual you use in your report.

Region

This setting allows you to specify the input format for the visual. You can either select Auto Detect, which identifies the browser language, or select any preferred language/region. The default value is English. The language and separator used in the number, date, and date-time columns is based on this selection.

Include In Comment Tuple

By default, Data Entry Matrix only includes SmartFilter columns with a single selected value in comments. If a SmartFilter has multiple values selected, those values are excluded. Use this setting to add SmartFilter columns with multiple selected values to your comments. This gives you a more complete record of the filter context for each data entry.

Enter the columns you want to include. Use the full Table.Column format, for example, DimProduct.BrandName. To include more than one column, separate each entry with a comma, for example, DimProduct.BrandName,DimProductSubcategory.ProductSubcategoryName.

Once set, Data Entry Matrix adds the selected values from those SmartFilter columns to your comments.

Fetch All Data Before Load

If it is enabled, you can fetch all data segments before displaying the visual. It may cause longer loading and rendering time.

Fetch More With Scroll

This setting results a dynamic data load. As you scroll down, close to the bottom of the visualization, a part of the remaining rows will be fetched.

Corner Filtering

It is possible to filter the row/column headers from each of the corners of the cells.

• [Clear]: Approved filtering can be canceled with this button.
• Search box: You can search among the items and select, filter accordingly.

The icon shows us if we have a filter or not.

Layout

By default, the DEMx is in Classic mode.

In the case of Excel, the visual is rendered differently as shown below and you have two options to place the values on rows and show totals at the bottom.

Turning values ON rows can be useful when you are using more measures in the matrix.

Classic rendering:

Excel rendering:

Values On Rows

If you have multiple measures in your DEMx, you can see them in separate columns by default.

With this setting you can show them in rows, as you can see below:

    à    

Display Single Values

This setting allows you to display a single value name as a column header. So, if you have only one measure in your DEMx, when you pull it into the matrix, it does not show its name in the column header by default. If you enable this setting, the measure name will be shown in the header:

Format String

You can overwrite the measure formatting defined in the SSAS model by setting this property. If you have multiple measures this setting will influence all of it.

Formatting the cell content with the # character:

Cell content	1258
Format string	#$
Formatted cell content	1258$

Auto-size Column Width

If you turn this setting ON, the visual will set the width for all your columns based on the widest one. You can specify exactly how many pixels you want your columns to consume. This will influence all your columns.

If you use this setting, it is worth considering using the Stretch Columns property, so, that columns will take up all the place that is available determined by the width of the matrix itself.

Horizontal & Vertical Cell Padding

You can control how many pixels space you want to have between the columns/ rows with this setting.

Horizontal & Vertical Header Padding

You can control how many pixels space you want to have between the header columns/ rows with this setting.

Text Size

You can define a general text size for the matrix. This will be overwritten if you set up varied sizes for your headers, values, or totals, respectively.

Font Family

This setting has a drop-down list with PowerBI-supported font types:

If Default / Custom option is selected in Font Family property, two textboxes appear, allowing to define any kind of font family.

Custom Font Family Name

This setting allows you to define any kind of font family or a custom font family.

You can give a URL for a font family. The URL of the CSS file for the custom font family can be defined with External CSS URL property.

External CSS URL

This setting allows you to set the URL of the CSS file. It is available only from the General menu.

See an example below:

Collapse Method

By default, the Double Click method requires the end user to double click on the upper group member to expand.

You can choose the Icon method (+/- icons in front of the members) if you like. In this case you need to click on those to collapse or expand the members.

With Both you can choose both the Double Click and the Icon as well.

Finally, you can turn OFF this feature completely.

The visual preserves the state of collapsed row or column and after opening the visual, it will be applied.

Every row and column are expanded when there is no enabled collapse method.

Collapse Icon

You can set the icon with this property, +/- or Triangle.

Bold Headers

If it is enabled, the header will be bold.

Selected Item Font & Background Color

This property sets the font/ background color of the rows or columns, which is selected from the Context Menu » Select menu item.

Stretch Columns & Rows

If your matrix has a width that is wider, compared to how much space the total width of the rows and columns would consume, you can force the visual to stretch them to take all available space. Meaning if they are enabled, they remove the space between the matrix and the frame of the visual.

Stretch Column property disappears, if [General] » Auto-size Column Width is disabled.

Selection Border Color & Width

You can control the appearance of the selected cell by modifying the border color and width settings.

Formatting Enabled (Context Menu)

This setting allows you to open a context menu on the header or content cells, by right clicking on it. You can use this functionality in Power BI Service, in Edit mode or in Power BI Desktop.

In case of missing VPservice visual, only the Drill Down/Up and Select items are available.

Menu Items in Header Area

Properties (Header Value Properties)

Label

You can give unique column names on the label of the cell.

You can rename the label of the selected column by typing a string or using JavaScript expressions.

Available operations that you can use: mathematical, date, html, JavaScript, and you can refer to another column from another table, as you can see in the example screen shot above.

If you want to use JavaScript expression with table column fields, you should put the desired columns into a SmartFilter Helper visual. You can refer to a column in this way: [TableName].[ColumnName].

If you use this kind of column reference, you must make sure to filter that column to a single value (by slicer on visual, page level filters), otherwise the label will show the given table and column names.

It can accept formulas as well. So, if you have a calculated sibling or measure in your DEMx, you can add a SmartFilter Helper visual and add the sibling/ measure to it, and by this way you do not need to pull it to DEMx.

You are also able to use conditional operators in JavaScript, but in this case, you need to start the expression with “=” operator, then use the following formula:

Condition ? IfConditionIsTrue : IfConditionIsFalse.

You can see an example for this formula below. In this example the [Year] should come from SmartFilter.

E.g.: 1

=([Time].[Year] ? ([Time].[Year] - 1) : "") + " - Budget"
It replaces [Time].[Year] with "" until slicer comes. Afterwards it will be replaced with slicer value and do a JavaScript eval() function on the formula in the end.

E.g.: 2 – a simple string concatenation

Instead of Actuals: [Time].[Year] - Actuals à This is a simple string concatenation. [Time].[Year] is empty until slicer value arrives, and it will be replaced afterwards.

Index

You can modify the order of the rows/columns, by setting the index.

à à

Drill Down/Up

This menu item opens a sub-Context menu from where you can do the drilling provided by Power BI.

Drilling is only possible on a level if there are at least two levels (at least one more).

Please see more detailed description about drilling in this article: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

Select

The selection color can be different from the other cells if you set the [General] » Selected Items Font Color and Selected Items Background Color properties.

It appears only when Corner Filtering setting is disabled.

The selection also filters other visuals on the same report page accordingly.

Add Computed Sibling

The added computed sibling row/ column/ measure appears between header cells and Totals. The position of the computed row/ column can be set in Context menu » Properties » Index.

Computed sibling can be used to display a new column that contains data, based on a given calculation expression. 

By adding a new computed row or column to your DEMx, it will be on the same dimension hierarchy level, where you opened the Context menu. 

How to set up?

1. Open Context menu » Properties on the new computed value (called [Computed sibling] in our example above) to enter the expression for the calculation.
2. You can use

• basic mathematical operators (like:  +, -,  /, *), 
• constant values
• refer to an element on the same hierarchy level by using square brackets. (e.g.: If the computed sibling is created on Month Name level, you can use an element of the Month Name for calculation.)

3. Click on [OK] to submit your settings.

You can remove the computed sibling, by Context menu » Remove Computed Value:

 

Add Computed Child

The added computed sibling row/ column appears between header cells and Totals. The position of the computed row/ column can be set in Context menu » Properties » Index.

It works like the Computed Siblings, but you can use it only on those dimensions’ hierarchy levels, where there is at least one child level.  

 Calculation of Computed child:

The main difference between Computed sibling and child is on hierarchy level: 

• Siblings are on the same level,
• Child is under the selected dimension.

Add Global Computed Child

The Add Global Computed Child setting allows you to add global variables, thereby providing the option of modifying values globally. The Add Global Computed Child is available through the context menu.

If you want to modify the properties of The Global Computed Child, follow these steps:

1. On the header cell, right click and select the “Properties” menu. The following pop-up dialog appears:

2. Modify the following attributes to apply changes globally:
   • Label
   • Computed Value Expression
   • Index

3. Click OK. The Global Computed Child values are set.

The example shown above, the Label is named as ‘TestLabelComputedChild’ in every level. Their value will be 25 and displayed at the first place amongst the level elements.

Hide

With this setting, you can hide a row or column in your matrix. You just need to select the proper row/ column and click on Hide, so it will disappear.

After clicking on Hide:

You cannot see that row anymore. With this setting a new button appeared on top of the matrix, in the button row, called [Show All]. If you click on this button, your hidden row (or column) appears again.

Cell Formatting

This property opens a window, called Cell Formatting, with the following opportunities:

You can use color-picker or color codes (HEX, RGB, HSL) for coloring.

You can set the font style to bold and/or italic.

You can choose a Font Family from the drop-down list.

You can set the Font Size with the arrows. Minimum value is 5, maximum value is 40.

There are properties to set the border – style, color, width – for every angle: Left, Right, Top, Bottom.

Border Style can be solid, dotted, dashed.

Combined CSS

You can use CSS for cell formatting by typing. Or what you set in the above properties in Cell Formatting, their CSS code is auto-generated, e.g.:

font-family: arial;
background-color: black;
font-size: 30px;
color: black;
border-left: 4px orange solid;

You can save your settings to a template, which you can use at a later phase. To do that, you need to click on the [Save Template] button, give it a custom name and finalize it by clicking on the [OK] button.

You can load the previously saved template, from the template list, by [Load Template] button.

The templates are stored inside the visual in JSON format. You can find them in [General] » Report Settings property. They are valid only in that DEMx.

If you changed your mind and do not want to create a special setting of your cell/ header, you can choose to reset the parameters, by clicking on the [Reset] button.

Context Menu opens with the following menu items	Properties	Drill Down/Up	Select	Add Computed Sibling	Add Computed Child	Add Global computed child	Hide	Cell Formatting	Remove Computed Value
Edit mode – on row/column header cells	+	+	+	+	+	+	+	+	–
Edit mode – on row/column computed header cells	+	–	–	+	–	–	+	+	+
Edit mode – on comment column header cells	+	–	–	–	–	–	+	+	–
Edit mode – on content cells	+	–	–	–	–	–	–	+	–
Reading View mode – on row/column header cells	–	+	+	–	–	–	–	–	–
Reading View mode – on row/column measure header cells	–	+	–	–	–	–	–	–	–
Reading View mode – on row/column computed header cells	–	–	–	–	–	–	–	–	–
Reading View mode – on comment column header cells	–	–	–	–	–	–	–	–	–
Reading View mode – on content cells	–	–	–	–	–	–	–	–	–

Column Resizing

If you enable this property, you are allowed to resize column width by dragging and moving its border (like in standard PowerBI matrix or in Excel).

In PowerBI Service it works both in ‘Reading’ and ‘Edit’ mode.

By double-clicking on the border, it will resize the column to its original width.

General Cell Border Width & Color

This property sets the border width & color of all cells of the visual.

Hide Corner

With this setting you can hide the corner cells of the visual.

Hide Empty Levels

If there is one measure in the visual and no rows or columns, the header of the measures and the corner cell which belongs to the measure will not be displayed.

Hide Empty Levels OFF:    Hide Empty Levels ON:

Report Settings

It contains a JSON string, with all format settings of the visual, like coloring, labels, collapse, order, format. If you delete its content, the default setting will be set automatically.

Visual Border Width & Color

You can set the outer border width and color around the visual.

Hide Button Row

You can hide the whole button row, buttons, and icons (e.g.: VPService icon, Fetch more icon) as well.

Visual Title

You can give a title of your matrix right above the corner and column header(s).

If you type a title, additional properties will appear and with their help you can make it unique: Visual Title Font Size, Visual Title Font Color,

Visual Title Background Color, Visual Title Padding

Settings Edit Delay

This property delays the refresh of the DEMx after changing a setting. It can be used to avoid those situations when you want to edit a property (e.g.: Visual Title, Connection, Web Service URL etc.) with data-input and in the middle of the typing the visual refreshes itself and drops the focus out of the input box.

The value of this property is in milliseconds and its value is set to 5000 by default.

Show Tooltip Report

If this property is turned ON and configured, then hovering on a cell, a tooltip page appears.

Configuration

To use the tooltip report feature, you need to set a page of your report as a tooltip.

1. Open your report.
2. Open the page you wish to set as a tooltip.
3. Open the format pane of the page without selecting any visual.
4. On the format pane, turn ON the [Page information] » Tooltip setting.
5. Select the page where the DEMx visual can be found.
6. Select the DEMx visual on the page.
7. Open the format pane and scroll down to the bottom. There you will find a property group, called [Tooltip].

8. Turn the setting ON.
9. Set [Tooltip] » Type property to Report page.
10. Set [Tooltip] » Page property to the name of that page you turned ON in Step 4.
11. Turn ON [General] » Show Tooltip Report setting. Hovering above a cell, it shows the page you set as tooltip.
12. Create a report on the tooltip page that you want to show.
    https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-tooltips

Consistent Tooltip Report

This setting is visible only if ‘Show Tooltip Report’ setting is turned ON.

This setting makes a tooltip report stay visible as long as the cell is selected, or any specific user event hides it.

These specific user events could be:

• pressing the “X” button at the top right corner of the tooltip report window
• deselecting the cell (optionally with [CTRL] + click)
• selecting another cell
• right-clicking on headers to open Context menu
• selecting another page in the report
• switching between ‘Edit’ mode and ‘Reading view’

The tooltip report is a pop-up window containing a report. This report receives its filter from the value of the selected cell.

The tooltip report must be set up on another page with enabling ‘Tooltip’ property:

Additional information:

• ’Consistent Tooltip Report’ property becomes visible only if ‘Show tooltip report’ is enabled.
  • If ’Consistent Tooltip Report’ is enabled, ’Consistent Tooltip Report Class’ setting appears.
  • If you enable ‘Consistent Tooltip Report', ‘Show Comment On Cell Selected’ property will be disabled automatically &
    If you enable ‘Show Comment On Cell Selected’, ‘Consistent Tooltip Report' property will be disabled automatically.
    The two settings cannot be turned ON at the same time.
• If ’Consistent Tooltip Report’ is turned OFF, the tooltip report appears only by moving the mouse pointer above the cell.
• If you have DEMx visual in your tooltip report,
  • please turn OFF
    • ‘Commenting’
    • ‘Tooltip’
• If you have Power ON write-back capable visual in your tooltip report & you do a write-back in the tooltip report, the change will be saved back and refreshed, when the main report is refreshed.
• Please do not use either VPService, or SmartFilter visuals in the tooltip report. (Tooltip report can use the VPService visual from the main page if necessary.)

Consistent Tooltip Report Class

This setting is visible only if ‘Show Tooltip Report’ and ‘Consistent Tooltip Report’ settings are turned ON.

It contains the CSS class name of the tooltip. If you modify it and the CSS name is invalid, the tooltip report will disappear.

The default value is: .tooltip-container.themeableElement.enhancedTooltips

Technical details:
The tooltip report is not a part of the DEMx visual, but a unique element of the Power BI application. The visual needs the CSS name of the Tooltip Report for modification.

This is just a “safety setting”, because when Microsoft changes the CSS name of the tooltip report, the user can modify the setting too.

Use Legacy Default Styles

This setting controls whether Data Entry Matrix reports use the legacy UI or the new UI introduced in version 25.3.

When upgrading to version 25.3, this setting is automatically turned on (using legacy styles). Turn off this setting to switch to the updated report UI.

New reports created in version 25.3+ use the new UI by default.

Position and Size

As for all visuals you can control the size of the matrix by setting Width and Height as well as the X and Y Positions. Setting the Width and Height influences how stretching can be applied.

Column Formatting

With these settings you can control the behavior and appearance of the columns in the visual.

Column Alignment

You can set the text alignment for each column header with this setting.

Column Collapse

If you have multiple fields added to the column collection, you can turn ON collapse on the top level so, the matrix will be rendered as the group is collapsed by default. If collapse is turned OFF, the visual will be rendered as all dimension members are expanded by default.

Collapsed is turned ON:

Collapsed is turned OFF:

You can control how collapse works, by setting the [General] » Collapse Method property.

Column Headers

You can customize the appearance of the column headers within this group, such as:

• Outline: you can set the border
• Font / Background Color
• Font Family: If you choose Default / Custom, an additional setting appears, called Custom Font Family Name and you can define any kind of font family, by giving a URL.
• Size
• Turn ON or OFF the Word Wrap for longer texts
• Setting the Vertical Alignment of the column headers from a drop-down list to Center, Top or Bottom.

Currently, you cannot specify the width individually for each of the columns. You can set the width for all the columns, or you can turn on auto column width as well in the General settings.

Column Sorting

You can control the ordering of the dimension members to be ascending or descending. Please also check the Sorting section of the Use Cases.

Column Totals

You can enable or disable the column totals with this setting.

Row Formatting

With these settings you can control the behavior and appearance of the rows in the visual.

Row Alignment

You can set the text alignment for each row header with this setting.

Row Collapse

If you have multiple fields added to the row collection, you can turn ON collapse on the top level so, the matrix will be rendered as the group is collapsed by default. If collapse is turned OFF, the visual will be rendered as all dimension members are expanded by default.

Collapsed is turned ON:

Collapsed is turned OFF:

You can control how collapse works, by setting the [General] » Collapse Method property.

Row Headers

You can customize the appearance of the row headers within this group, such as:

• Outline: you can set the border
• Font / Background Color
• Font Family: If you choose Default / Custom, an additional setting appears, called Custom Font Family Name and you can define any kind of font family, by giving a URL.
• Size
• Setting the Row Header Width
• Turning ON / OFF the Word Wrap for longer texts in row header values
  • You can scroll in the row header area when your mouse pointer is above it.
• Setting the Vertical Alignment of the row headers from a drop-down list to Center, Top or Bottom.

Row Sorting

You can control the ordering of the dimension members to be ascending or descending. Please also check the Sorting section of the Use Cases.

Row Totals

You can enable or disable the row totals with this setting.

Conditional Formatting

You can set up custom background colors for your measures based on the values.

You need to define how many different colors you want use by setting the Region Count property. You can define maximum 10. After giving it, you need to determine the boundaries for each of them by setting the Bottom and Top values.

In this example, the first color – a light blue – will be applied as a background for those cells that have values between 300 000 and 400 000.

Measure Name Source Measure Display Name

The conditional formatting rule can be based on a referenced value (another measure), by the following way:

When the Region Count of the measure (Quantity_E) is set to 0, a source measure (Price_E) can be provided and based on this source you can set coloring regions.

The source measure that you are referring to must be added to the Data Entry Matrix.

If you like, you can hide that source measure, by enabling its [Hide Measure] property.

You need to set the region(s) (boundaries and colors) for the source measure.

In our example Source Measure is Price_E, we have one region, between 2 and 5.\

Its result can be seen below:

Formatting Measure

This feature is like conditional formatting but uses templates from ‘Cell Formatting’ via DAX measure. Through the example below you will be able to understand how you can use this feature. We combine conditional formatting with styling.

1. Open your DEMx related report in Power BI Desktop
2. Set the following styles:
   1. 
      Select the DEMx visual
   2. Right click on a cell à ‘Cell Formatting’ should be seen and if you click on it, a window opens with settings possibilities, like font color, background color, font family, size, borders, etc.
   3. Set the styling as you like. In our example we have set a background color (green) and we made it bold.
   4. 
   5. Scroll down to the bottom of the window and save your settings as a template. Click on [Save Template], then OK button.
   6. 
3. Create a DAX formatting measure (in our case we call it StylingMeasure) that returns the name of at least one template in string format:

4. Add the measure to the DEMx as a value. You should see this:

5. Enable StylingMeasure as Formatting Measure property à StylingMeasure columns disappear from the matrix and the previously set styling will be valid for the whole matrix, both of our measures (Quantity_E; Price_E), evaluating the definition of StylingMeasure.

6. An additional, empty property appears with this setting, called StylingMeasure Target Measure Display Name.
7. What you give in here, for that measure (Quatity_E; Price_E) the setting will be valid.
8. With Quantity_E:

9. With Price_E:

10. Save your modified report and publish it.

When one of the measures formatting is ON, the column for that measure should not appear.

Hide Measure

You can hide the current measure(s) from the DEMx, by enabling this property.

Even if this setting is turned ON for any of the measures, the [Show All] button will not appear on top of the matrix, in button row.

Dynamic Totals

If it is enabled for a particular measure in DEMx, then while you are typing the new value into a cell, or using a smart formula, all its (sub-)totals, in rows and columns are changing accordingly.

        

If this property is turned ON for a measure, ‘Aggregation Method’ can be picked:

• AVG: average
• SUM: summary of all the values
• MIN: minimum of the values
• MAX: maximum of the values
• Weighted AVG: weighted average
  In this case ‘Weight Measure Display Name’ should be determined, and added to DEMx. Meaning we define based on what we would like to calculate the weighted average.

Important: The weighted average setting (also MIN, MAX) is only for showing purposes, during modification. After saving the changes not weighted average total will be saved back to the database, but the basic aggregation (if we pulled a summary measure to DEMx, then summary total; in the case of average, the average total).

E.g.:    After saving the changes:

Dynamic Spread

Measure Name Dynamic Spread

If the (sub-)total value is changed, it updates the detailed cell numbers, based on the spreading. Meaning it maintains the original ratio within the aggregation group. (You can read more about writing back to totals in this chapter.)

The spreading result is relevant only in SUM (summary) type of aggregation.

Note! The updated lower-level numbers are only displayed, but they are not sent as part of the save request. When a lower-level cell is changed that has an updated displayed value, it will be used as the oldValue in the save request.

Dynamic spread can be calculated with “E” smart formula in another way as the backend. It does not calculate in proportion to the SUM of the values of the rows in the fact table, it just simply equally divides the number indicated by the formula (See more information about smart formulas in this chapter.)

Cascade Spreads to Lowest Level

This setting in Data Entry enables dynamic spreading at the lowest granularity; the total values propagating down to the lowest level. When you modify a yearly figure, the change automatically spreads to the month level. If this setting is disabled, spreading occurs only at the total level.

Fix Totals

Measure Name Fix Totals

By enabling this setting, you can modify one or more cells, while the corresponding (sub-)totals remain the same value, only all belonging detailed values will be recalculated.

Any data point has been modified, the (sub-)total value is always 100%.

After enabling Qty Fix Totals:

    à    

Highlight

You can highlight the cells in the matrix in different situations, by giving them other, arbitrary background and font colors.

Hover Highlight

If it is enabled, you can see where your mouse pointer is when it is above the matrix. The background and font color of the cells will be highlighted, as you are moving the mouse.

It uses the same colors that have been set in Highlight Font and Background Color properties.

Header-Content Connection Highlight

If it is enabled, and you click on a cell in the Data Entry Matrix, its headers will be highlighted.

Totals Highlight

If it is enabled, the (sub-)total values of the selected cell will be highlighted.

Total Connections Highlight

If it is enabled, when you select a (sub-)total cell, the cells from which it is calculated, will be highlighted.

Spread Highlight

If it is enabled, when you select a (sub-)total cell, the cells

• which are on the next lower level and
• which will be modified if the selected (sub-)total will be modified

will be highlighted.

Sibling Highlight

If it is enabled, when you select a (sub-)total cell, which has a sibling (sub-)total(s), it (they) will be highlighted.

Labels

If you do not like the original text that is used in Data Entry Matrix (e.g.: [Save Changes], [Discard Changes], Totals), you can change them here.

Save Changes Label

Discard Changes Label

Save Comment Label

Set Goal Seek Label

Reload Data Label

Refresh Comments Label

Fetch More Label

    

Reset Filters Label

Show All Label

Drill Through Window Title

Column Grandtotal Comment Label

Totals Label

Comment Label

If you would like to edit a cell that has a pop-up comment above the cell, with [ESC] you can make it disappear.

This is how the renaming labels can look like:

Custom Corner Label

If you want a specific text to the corner of the matrix, not the original row/ column names coming from the tables, then you can set a custom label for the whole corner.

Totals

In the Totals property group, you can control:

• Show Totals: Whether to show totals or not
• Collapse To Totals: Collapse the rows/columns and display just the Totals of the corresponding rows/columns.
• Bold Totals: Set the font style of the Totals bold.
• Outline: you can set the borders
• The Primary and Secondary Font & Background Colors – for alternating
• Show Sub-, and Grandtotals on Rows & Columns as well
• Subtotals At The Bottom OR On The Right: Inserts the subtotal under the corresponding rows. Inserts the subtotal to the right of the corresponding columns.

Values

In the Values property group, you can control:

• Font Family: If you choose Default / Custom, an additional setting appears, called Custom Font Family Name and you can define any kind of font family, by giving a URL.
• Size
• Outline: you can set the borders
• The Primary and Secondary Font & Background Colors – for alternating
• The Primary and Secondary Font & Background Colors for Disabled: read-only cells
• The Primary and Secondary Font & Background Colors for Modified values: those cells where the values have been changed
• Max Edit Value Decimals: The maximum decimal places shown when you edit the cell value
• Word Wrap: New setting for values wraps text.

Grid (Settings)

You can further customize the appearance of the matrix by specifying the parameters under this configuration collection.

Setting the outline color and width affects the borders between the row/column headers and the cells:

Setting the vertical grid properties, you can modify the borders between columns:

Horizontal grid settings affect the borders between rows:

Importance of SmartFilter Helper Visual

It is a general functionality in Power BI that when you are using slicers, page-report, or visual level filters the visual itself is not aware that it is receiving a filtered dataset. For the writeback to work properly if you are using any of these filtering capable elements, you need place SmartFilter helper visuals in your report, so that the DEMX will be aware of these, and the write-back engine will take these into consideration when it composes the SQL statements. Without it, slicer selection will be left out of the tuple that is being sent back to the service, it will be missing from the SQL statement, hence the saved result will be wrong. It is important that you use the visual that is shipped with the setup kit, as you can find a visual with the same name in the Microsoft Visual Store but that does not have this functionality.

This visual is invisible to the end users as it has no background, no borders and the values are not shown.

General rules:

• If you use a field that is not used in your matrix and originated from a different dimension (table), put a SmartFilter into your report with the same field.

Example: Let us assume you have a slicer on Customer Group Name, but the ‘Customer’ table is not used at all in DEMx. In this case add a SmartFilter helper visual in your report and place the same field into it (in this case the Customer Group Name).

• If you have more slicers which fields are originated from the same table, put a SmartFilter into your report and use the lowest granularity field in it.

Example: Let us assume you have slicers on Size and Brand which are coming from the ‘Product’ table. In this case add a SmartFilter helper visual in your report and put the ProductId in it. (ProductId is on the lowest granularity level.)

• Exceptions when you do NOT(!) need to put a SmartFilter into your report:
• If the field or a lower granularity field from the same table is already on the field list of DEMx
• Example:
  • If you have a slicer or filter for Product.Brand &
  • Product.Brand or Product.SKU is in DEMx field list.
• If the field has lower granularity than data in your write-back table. In this case you only need to add a SmartFilter with a field that corresponds to the granularity of the write-back data.
• Example:
  • If the granularity of your write-back data is based on Product.Brand
  • You do not need to add a SmartFilter for Product.SKU only for Product.Brand.
• If the field is already determined by the combination of one or more SmartFilters or DEMx fields.
• Example:
  • If you have a slicer for Time.Quarter & Time.Year and Time.Month are in DEMx field list.
  • If you have a slicer or filter for Account.Name & Account.ID is in DEMx field list.
• If there are multiple SmartFilters with slicers in a report containing a Data Entry Matrix, not all of them may load onto the page before you modify data in the matrix. This can result in an inappropriate value being saved to the database. To avoid such issues,
  • set "SmartFilters On Page" to match the number of SmartFilters on the report page.
  • enable "Force Slicer Resend" on all SmartFilters.
    Therefore, the “Save Changes” button is disabled until all slicers and SmartFilters are loaded onto the report page. It allows Data Entry Matrix to keep track of the number of SmartFilters.
    With zero or empty value the visual behaves as in previous versions. This setting works only with the latest SmartFilter Helper visual (v24.1).

From a performance perspective, it is better to use multiple SmartFilters with higher granularity fields than one SmartFilter with a low granularity field. (e.g.: If there are slicers or filters on Product.Brand and Product.Size, it is more performant to add SmartFilters for these, instead of adding a SmartFilter for Product.SKU.

Smart Formulas

You can invoke smart formulas on cells. Instead of typing exact values, you can use the following formulas:

• inc# : increases the value by the given # number
• inc%# : increases the value by the given # percentage
• dec# : decreases the value by the given # number
• dec%# : decreases the value by the given # percentage
• mul# : multiplies the value by the given # number
• div#: divides the value by the given # number
• <# : copies the entered # value all to the left to all columns in the given row
• #> : copies the entered # value all to the right to all columns in the given row
• v#: copies the entered # value until next total down in the given column
• ^#: copies the entered # value until next total up in the given column
• E# : only usable on total or sub totals. Will evenly distribute the # number between the detail rows in the aggregation group instead of proportional distribution. If there are different number of fact rows behind each cell, the cells will have different values after save.

Multiple Smart Formulas can be applied in the same cell e.g.: <inc5 vdec10% >mul3 ^inc8

Write-back on Totals

The Data Entry Matrix has a unique feature when it comes to writing back on totals or subtotals. Consider the following example where we have four products in the APPLE brand

As you can see the 274k total is a sum of the four products. There is a rate between these products in terms of how much they contribute to the total. Whisky gives the most, as 126 640 / 274 445 is 46% of the total, and Cola KEG contributes the least with its 14%. If you write-back on a total or subtotal, by default the service will try to distribute the modified value honoring the original ratio between the products. When you change the subtotal value to 300 000 for example, 46% will be allocated for the Whisky product.

Tip: if you want to distribute the total value by a predetermined ratio here is a trick that you can apply. Before modifying the total, set a percentage figure for each of the elements inside that aggregation group like below and save changes.

Then you can set a value for your total. In this case 10% will be allocated for Apple 0.5L bottle, and 30% for the Whisky product. Note: this technique will not work on complex measures.

You can force the service to use even distribution by using the smart formula E#. If you enter E400000 in this example, 100 000 will be allocated for each of the products, as there are 4 items in this aggregation group.

Note: If you use a separate write-back table with default values displayed if there is no written back record on that tuple, writing back on totals will only influence those cells where there are saved records for that tuple in your write-back table.

Goal Seek

You can use the goal seek functionality to only modify selected cells to achieve a target goal.

Consider the example below, where we want to increase total sales by 10% for the APPLE brand for April month, but by only modifying the Apple 0.5l bottle and Cola KEG products. You can do this by selecting these product cells and marking them for goal seek by clicking on Set Goal Seek followed by an inc10% smart formula applied on the subtotal row. When you click on save changes, that 10% increase will be distributed amongst these two products.

You can turn OFF this functionality in the Goal seek settings collection. You can also set the coloring theme for the cells that are marked to participate in the operation.

Important note: You can only use the goal seek feature on those measures and cells which are in relation to each other, and the operation is arithmetically possible. For example, if you set goal seek on a cell which is not included in the measure of the target (the cell you modify) then the operation will not complete.

Goal Remains After Save

If it is enabled, after the Goal Seeking feature has been used, it shows the modified cells:

After saving, [Discard Changes] button remains alive. If you click on it, the highlight of goal-sought values disappears.

Securing Cells

Write Security Measure

You can control which cells are editable and which are not in the visual by creating a measure that will be evaluated on the cells, but its result will be solely used for this security purpose. This measure can be deployed into your SSAS model, or it can be an embedded measure in the report itself.

So, at the end you will have two at least two measures in your matrix as below can be seen:

The Quantity_E measure is shown and can be written back, and the result of the WriteSecurityOnProduct measure is used to control which cell is read-only. If you turn the Write Security setting ON for this measure, it will be hidden and will be used to determine the security.

The logic of this security measure:

If the DAX expression returns an empty string (“”) or BLANK(), the visual considers this cell to be read-only. If it returns anything else – like 1 or TRUE(), it considers this cell writeable.

Disadvantage of BLANK(): the visual might filter out those rows, where the displayed measure also does not have a value. Look for show members with no values setting in your report.

Disadvantage of empty string: it can cause the “crossjoin” behavior.

Consider this example:

CanWrite :=
MAXX (
    Sales;
    IF (
        AND (
            OR (
                SELECTEDVALUE ( 'Time'[ShortMonth] ) = "Mar - 15";
                SELECTEDVALUE ( 'Time'[ShortMonth] ) = "Jun - 15"
            );
            SELECTEDVALUE ( 'Product'[SKU] ) = "H02"
        );
        "";
        1
    )
)

It will prohibit modification on cells that belong to the H02 Product and contains data for March and June in 2015.

You can implement more complex securities with this technique such as control permissions by user – entity mapping (which user can edit which product). Please refer to the following article that contains a detailed example on how to implement such a use case:

https://support.poweronbi.com/portal/kb/articles/implement-complex-write-security-per-entity

Measure Alignment

Set the horizontal alignment of the measure label.

Read Only Values

This is a separate property group. You can make a measure read-only in the DEMx, validly for the entire matrix. Enabling this property and from that point that measure is not editable anymore.

SQL Row Level Security Policies

You can implement RLS policies on your fact tables to further tighten the security. Please refer to the following article in our Knowledge Base:

https://support.poweronbi.com/portal/kb/articles/control-write-back-permissions-on-back-end-sql-server

Commenting

If the feature is enabled, you can create comments on each cell and they will be saved to the underlying SQL database, to dbo.Comments table, by clicking on [Save Changes] button.

[Save Comment] button appears only, if [Data Entry] » Read only property is enabled. In this case you can add comment to cells but cannot modify the values in the matrix.

The dbo.Comments table is created automatically by the write-back engine inside your SQL database at the very first time, when the feature has been enabled.

A comment record contains:

• Tuple – on which cell the comment has been created
• DateTime
• User
• Text

Settings

Commenting Enabled: If the setting is OFF, ALL triangles and comment settings are hidden.

Automatic Saving: If it is enabled, comments are automatically saved after changes. If you start typing into the comment input field and stop typing or leave the selected cell, the comment is saved automatically. 

Comment Triangle Color: You can select a color for the comment symbol (triangle). 

Modified Comment Triangle Color: You can select a different color for modified or newly created comments symbol (triangle).

[Save Changes] and [Discard Changes] buttons become active as soon as you start typing/ modifying a comment.
Comment Triangle Size: Size of triangle in pixels

Auto Refresh Comments: If it is enabled, comments automatically refresh without the need of pressing the ‘Refresh Comments’ button. 
Show Comments On Cell Select: If it is enabled when a cell selected and there is comment, the popup will automatically be rendered.
Show Date: If it is enabled, displays the date of the comment’s creation/last update. 

Show Grandtotal Comment Column: If it is enabled, ‘Comment’ column becomes visible in the DEMx, otherwise it is hidden.

Show Grandtotal Comment Column Triangle: This setting is visible only if Show Grandtotal Comment Column is enabled. If it is enabled, triangles become visible on GrandTotal Comment Column, otherwise they are hidden. This setting ensures you to be able to see who created the comment and when.

Show Grandtotal Comment Triangle: This setting is visible only if Show GrandTotal Comment Column is enabled. If it is enabled, triangles become visible even if GrandTotal Comment Column is visible, otherwise the triangles are hidden.

By default, the last comment will be persisted on a cell if you overwrite it. However, by leveraging SQL triggers, you can preserve the comment history. Please refer to this article on how you can implement it: https://support.poweronbi.com/portal/kb/articles/preserve-comments-in-the-same-cell-aka-saving-comments-history

Grandtotal Comment Column Auto Size: This setting is visible only if Show GrandTotal Comment Column is enabled. Turning ON this property will always automatically set the width of this column to fit its content.

While it is turned OFF its width can be set only with Grandtotal Comment Column Width. This property is visible only if Grandtotal Comment Column Auto Size property is disabled.

Disable Comment Error Message: Turning ON this property will disable Refresh comments error message in Power BI desktop.

Debug

Diagnostic Mode

Turning it ON, it reveals debug settings.

Display Fetch More Icon

Enabling this setting, a “+” icon appears in front of the VPConnection icon ( ), in the upper right corner of the DEMx while scrolling down, showing us, there are more values to fetch. When you reach the last value, the icon disappears.

A tooltip also appears, when hovering the icon, saying Additional rows available.

Use Cases

This chapter describes typical uses cases that you can implement in your environment to enrich Data Entry Matrix feature leveraging native database objects. Chapter references Microsoft’s SQL Server’s features.

Versioning

Please refer to the following articles in our knowledge base regarding versioning:

• https://support.poweronbi.com/portal/kb/articles/implement-versioning
• https://support.poweronbi.com/portal/kb/articles/implement-version-management

Custom Validation

You can create complex validation logic by leveraging SQL server features, more precisely triggers to check certain conditions and send back messages to the client in case of violation of a business rule. The core logic of the trigger can contain anything you like, only the return method of your message needs to be in a certain format. The following example shows how to return a custom message in a trigger:

IF (@YourCondition)

   THROW 50001, N'<SQLError>Cannot save modifications due to violation of business rules.</SQLError>', 1

Auditing

This chapter will provide you a guide how to capture the username who is modifying the current measure. Please refer to the following article in our Knowledge Base:

https://support.poweronbi.com/portal/kb/articles/get-user-name-on-back-end-during-write-back-sql-server

For example, if you modify a cell that is an aggregation of multiple records of the Fact table, you can create a FactAudit table with the same structure as the table in question with columns that can contain extra information about the operation, like:

• ModifiedBy – the user’s name
• ModifiedAt – the date
• OldValue
• NewValue

The following trigger will capture the actual user’s name and the current date, and save it to an audit table:

CREATE TRIGGER [dbo].[trg_FactAudit] on [dbo].[Fact]

AFTER INSERT, UPDATE

AS

BEGIN

 

    DECLARE @USERNAME VARCHAR(255)

 

    SET  @USERNAME = CAST(SESSION_CONTEXT(N'user_name') as varchar(255))

 

    INSERT INTO [dbo].[ProductAudit]

    SELECT

    [SKU]

    ,@USERNAME

    ,GETDATE()

    ,yourFactTableColumns

    FROM INSERTED

END

Please note that writing back to a measure can influence multiple rows in the underlying fact table. If you implement this solution your audit table can grow quickly depending on the granularity of your fact table. Consider using the Tracing feature or enabling the SaveWriteBackHistory setting as described in the Advanced Settings chapter.

Performance Optimization

In the case of large fact tables reprocessing in-memory SSAS models can take some time. The Write-Back Service can determine which portion of your data should be processed. For that, you can implement special partitioning. Please refer to the following article in our Knowledge Base for examples, walkthroughs, and tips:

• https://support.poweronbi.com/portal/kb/articles/performance-tips-for-direct-query-mode
• https://support.poweronbi.com/portal/kb/articles/performance-optimization-tips
• https://support.poweronbi.com/portal/kb/articles/implement-ssas-partitioning-for-improved-write-back-performance
• https://support.poweronbi.com/portal/kb/articles/dedicated-table-for-write-back-using-ssas-partitioning

Models with Calculated Tables and Columns

As described in the Limitations chapter, you cannot write-back on calculated tables and columns. You can have these types of objects inside your SSAS model, but by default, the Write-Back Service always does a model metadata check before saving changes, and if it finds such structures will prohibit the process. If you are certain that you do not try to write-back on measures that are using calculated columns, and you do not use calculated tables in your matrix (neither on rows nor columns), also these objects are not used in any kind of filters (slicers, visual level filters, etc.), you can turn this strict checking OFF, by using Advanced Configurations.

Sorting

If you do not achieve the desired result when it comes to the built-in sorting functionality, you can still try out the following:

You can use PowerBI built in sorting functionality:

Or you can set up a Sort By Column parameter in your SSAS model in Visual Studio or Tabular Editor.

Advanced Configuration

You can control the behavior of the Write-Back Service for each SSAS model. For this, you need to create a dedicated SQL table in a strict format. It is important to know that if you have multiple data sources in your SSAS model, you need to tell the write-back engine which data source it should look for in this setting table. The way to do it is to add the “WriteBack” – case-sensitive string in the name of your data source where you will be creating this helper table. So, for instance, if you have three data sources defined (ds_A, ds_B and ds_C) for three different SQL databases (Database_1, Database_2, Database_3) and you want to create this settings table in Database_2 – which is used in ds_B, then rename it to be ds_B_WriteBack for instance.

The structure of the table is:

You cannot rename the table or use another schema than dbo now. Be aware, that the [Value] column is a sql_variant datatype, so different settings will have different types (like bit, varchar, numeric, etc.) It means that when you try to copy this table by the Generate Script tasks in SQL Management Studio it will not recognize the correct data types and might result in a non-compatible setting. If you need to move/copy this table – or the entire database – always re-create this helper table with the appropriate data types by using the correct insert statements.

Below you will find the most important settings, for the complete list please refer to this article:

https://support.poweronbi.com/portal/kb/articles/advanced-configuration

Important settings:

1. Turn strict metadata checking OFF (SkipCalculatedRelationShips; SkipInvalidMeasures): If you have calculated tables, relationships, then the service will prohibit the operation by default. If you turn this OFF, the operation will not be blocked. Be careful that you will not use unsupported objects (e.g.: calculated column as a row / column member in your matrix or as slicers, etc.) during the write-back.
2. EnableTracing: if you enable this, a Trace.Txt file will be created in the folder at the location of the webservice which will log all operations during write-back.
3. SaveWriteBackHistory: if you enable this, a dbo.WriteBackHistory table will be created automatically in your database, by the write-back engine after the following write-back. (In case of multiple data sources, it will use the connection with the ‘WriteBack’ tag.)

The table contains the following information for each cell modification:

1. ChangeDate – time of change
2. ChangedBy – name of the user who committed the change
3. Tuple – the cell’s tuple or coordinate
4. OldValue – original value of the cell
5. NewValue the updated value of the cell.

For point 1:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipInvalidMeasures', CAST(1 as bit)) --Don't throw error on non-parsable DAX expressions

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SkipCalculatedRelationships', CAST(1 as bit)) --Don't throw error if calculated column used in relation

For point 2:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'EnableTracing', CAST(1 as bit)) -- creates Trace.txt inside the web service's folder and log all operations

For point 3:

INSERT [dbo].[AdvancedSettings] ([Setting], [Value]) VALUES (N'SaveWritebackHistory', CAST(1 as bit)) -- creates dbo.WritebackHistory table and logs cell changes