DataViewMappings

The article is outdated, read updated article about data view mappings in official documentation of Power BI.

A dataViewMappings describes how the data roles relate to each other and allows you to specify conditional requirements for the them. There is a section for each of the dataMappings.

Each valid mapping will produce a DataView, but currently we only support performing one query per visual so in most situations you will only get one DataView. However, you can provide multiple data mappings with different conditions which allow

"dataViewMappings": [
    {
        "conditions": [ ... ],
        "categorical": { ... },
        "single": { ... },
        "table": { ... },
        "matrix": { ... }
    }
]

Important to note that Power BI creates a mapping to a DataView if and only if the valid mapping is filled in dataViewMappings.

In other words if categorical is defined in dataViewMappings but other mappings such as table, single, etc. are not, as in the following example:

"dataViewMappings": [
    {
        "categorical": { ... }
    }
]

Power BI will produce a DataView with a single categorical mapping (table and other mappings will be undefined):

{
    "categorical": {
        "categories": [ ... ],
        "values": [ ... ]
    },
    "metadata": { ... }
}

Conditions

Describes conditions for a particular data mapping. You can provide multiple sets of conditions and if the data matches one of the described sets of conditions the visual will accept the data as valid.

Currently, for each field you can specify a min and max value. This represents the number of fields that can be bound to that data role.

Note: if a data role is omitted in the condition, it can have any number of fields.

Example 1

By default, you can drag multiple fields into each data role. In this example we limit category to 1 data field and measure to 2 data fields.

"conditions": [
    { "category": { "max": 1 }, "y": { "max": 2 } },
]

Example 2

In this example, one of two conditions are required. Either exactly 1 category data field and exactly 2 measures, or exactly 2 categories and exactly one measure.

"conditions": [
    { "category": { "min": 1, "max": 1 }, "measure": { "min": 2, "max": 2 } },
    { "category": { "min": 2, "max": 2 }, "measure": { "min": 1, "max": 1 } }
]

Single Data Mapping

Single data mapping is the simplest form of data mapping. It accepts a single measure field and gives you the total. If the field is numeric it will give you the sum. Otherwise it will give you a count of unique values.

To use single data mapping you simply define the name of the data role you want to map. This will only work with a single measure field. If a second field is assigned no data view will be generated so it is also good practice to include a condition that limits the data to a single field.

Note: This data mapping cannot be used in conjunction with any other data mapping. It is meant to reduce data into a single numeric value.

Example

"dataViewMappings": {
    "conditions": [
        { "Y": { "max": 1 } }
    ],
    "single": {
        "role": "Y"
    }
}  

The resulting data view will still contain the other types (table, categorical, etc), but each mapping will only contain the single value. Best practice is to just access the value inside of single.

Categorical Data Mapping

Categorical data mapping is used to get one or two independent grouping of data.

Example 1 Here is the definition from our previous example on DataRoles.

"dataRole":[
    {
        "displayName": "Category",
        "name": "category",
        "kind": 0
    },
    {
        "displayName": "Y Axis",
        "name": "measure",
        "kind": 1
    }
]

Now for the mapping:

"dataViewMappings": {
    "categorical": {
        "categories": {
            "for": { "in": "category" }
        },
        "values": {
            "select": [
                { "bind": { "to": "measure" } }
            ]
        }
    }
}

This is a very simple example, in plain english it reads “Map my category DataRole so that for every field I drag into category, its data is mapped to categorical.categories. Also map my measure DataRole to categorical.values.”

for…in - For all the items in this data role, include them in the data query.
bind…to - Produces the same result as for…in, but expects that the DataRole will have a condition restricting it to a single field.

Example2

In this example, we will use the first two DataRoles from the previous example, additionally defining grouping and measure2.

"dataRole":[
    {
        "displayName": "Category",
        "name": "category",
        "kind": 0
    },
    {
        "displayName": "Y Axis",
        "name": "measure",
        "kind": 1
    },
    {
        "displayName": "Grouping with",
        "name": "grouping",
        "kind": 0
    },
    {
        "displayName": "X Axis",
        "name": "measure2",
        "kind": 1
    }
]

Now for the mapping:

"dataViewMappings":{
    "categorical": {
        "categories": {
            "for": { "in": "category" }
        },
        "values": {
            "group": {
                "by": "grouping",
                "select":[
                    { "bind": { "to": "measure" } },
                    { "bind": { "to": "measure2" } }
                ]
            }
        }
    }
}

Here the difference is in how we are mapping categorical.values. We are saying “Map my measure and measure2 DataRoles to be grouped by the DataRole grouping.”

Example3 Here are the dataRoles.

"dataRoles": [
    {
        "displayName": "Categories",
        "name": "category",
        "kind": 0
    },
    {
        "displayName": "Measures",
        "name": "measure",
        "kind": 1
    },
    {
        "displayName": "Series",
        "name": "series",
        "kind": 0
    }
]

Here are the dataViewMappings.

"dataViewMappings": [
    {
        "categorical": {
            "categories": {
                "for": {
                    "in": "category"
                }
            },
            "values": {
                "group": {
                    "by": "series",
                    "select": [{
                            "for": {
                                "in": "measure"
                            }
                        }
                    ]
                }
            }
        }
    }
]

The categorical dataview could be visualized like this.

PowerBI will produce you this as the categorical dataview. This is the set of categories.

Each category maps to a set of values as well. Each of these values is grouped by series, which is years. For example, Canada sales in 2013 is null, Canada sales in 2014 is 50.

Table Data Mapping

The table data view is a simple data mapping. Essentially, it is a list of data points, where numeric data points could be aggregated.

Example1

With the given capabilities:

"dataRoles": [
    {
        "displayName": "Values",
        "name": "values",
        "kind": 2
    }
]

"dataViewMappings": [
    {
        "table": {
            "rows": {`
                "for": {
                    "in": "values"
                }
            }
        }
    }
]

The table dataview could be visualized like this.

PowerBI will produce you this as the table dataview. Do not assume there is an ordering.

The data can be aggregated by selecting the desired field and clicking sum.

Matrix Data Mapping

Matrix Data Mapping is similar to table data mapping, but rows are presented hierarchically. And one of dataRole values can be used as a column header value.

{
    "dataRoles": [
        {
            "name": "Category",
            "displayName": "Category",
            "displayNameKey": "Visual_Category",
            "kind": "Grouping"
        },
        {
            "name": "Column",
            "displayName": "Column",
            "displayNameKey": "Visual_Column",
            "kind": "Grouping"
        },
        {
            "name": "Measure",
            "displayName": "Measure",
            "displayNameKey": "Visual_Values",
            "kind": "Measure"
        }
    ],
    "dataViewMappings": [
        {
            "matrix": {
                "rows": {
                    "for": {
                        "in": "Category"
                    }
                },
                "columns": {
                    "for": {
                        "in": "Column"
                    }
                },
                "values": {
                    "select": [
                        {
                            "for": {
                                "in": "Measure"
                            }
                        }
                    ]
                }
            }
        }
    ]
}

Power BI creates hierarchical data stucture. The root of tree includes the data from the first column of Category data role with children from the second column of data role.

Sample dataset:

Parents	Children	Grand children	Columns	Values
Parent1	Child1	Grand child1	Col1	5
Parent1	Child1	Grand child1	Col2	6
Parent1	Child1	Grand child2	Col1	7
Parent1	Child1	Grand child2	Col2	8
Parent1	Child2	Grand child3	Col1	5
Parent1	Child2	Grand child3	Col2	3
Parent1	Child2	Grand child4	Col1	4
Parent1	Child2	Grand child4	Col2	9
Parent1	Child2	Grand child5	Col1	3
Parent1	Child2	Grand child5	Col2	5
Parent2	Child3	Grand child6	Col1	1
Parent2	Child3	Grand child6	Col2	2
Parent2	Child3	Grand child7	Col1	7
Parent2	Child3	Grand child7	Col2	1
Parent2	Child3	Grand child8	Col1	10
Parent2	Child3	Grand child8	Col2	13

Core Matrix visual of Power BI renders it like a table.

Matrix visual

The visual gets data stucture as described below (only the first two rows are presented):

{
    "metadata": {...},
    "matrix": {
        "rows": {
            "levels": [...],
            "root": {
                "childIdentityFields": [...],
                "children": [
                    {
                        "level": 0,
                        "levelValues": [...],
                        "value": "Parent1",
                        "identity": {...},
                        "childIdentityFields": [...],
                        "children": [
                            {
                                "level": 1,
                                "levelValues": [...],
                                "value": "Child1",
                                "identity": {...},
                                "childIdentityFields": [...],
                                "children": [
                                    {
                                        "level": 2,
                                        "levelValues": [...],
                                        "value": "Grand child1",
                                        "identity": {...},
                                        "values": {
                                            "0": {
                                                "value": 5 // value for Col1
                                            },
                                            "1": {
                                                "value": 6 // value for Col2
                                            }
                                        }
                                    },
                                    ...
                                ]
                            },
                            ...
                        ]
                    },
                    ...
                ]
            }
        },
        "columns": {
            "levels": [...],
            "root": {
                "childIdentityFields": [...],
                "children": [
                    {
                        "level": 0,
                        "levelValues": [...],
                        "value": "Col1",
                        "identity": {...}
                    },
                    {
                        "level": 0,
                        "levelValues": [...],
                        "value": "Col2",
                        "identity": {...}
                    },
                    ...
                ]
            }
        },
        "valueSources": [...]
    }
}

Data Reduction Algorithm

A DataReductionAlgorithm can be applied if you want to control the amount of data received in the DataView.

By default all Custom Visuals have the top DataReductionAlgorithm applied with the “count” set to 1000 dataPoints. This is equivalent to setting the following properties in the capabilities.json:

"dataReductionAlgorithm": {
    "top": {
        "count": 1000
    }
}

You can modify the ‘count’ value to any integer value up to 30000. R-based custom visuals can support up to 150000 rows.

Data Reduction Algorithm types

There are four types of DataReductionAlgorithm settings::

top - if you want limit the data to values taken from the top of the dataset. The top first “count” values will be taken from the data set.
bottom - if you want limit the data to values taken from the bottom of the dataset. The last “count” values will be taken from the data set.
sample - reduce the dataset by a simple sampling algorithm limited to a “count” number of items. It means first and last items are included, as well as a “count” number of items having equal intervals between them. For example if you got a data set [0, 1, 2, … 100] and a count: 9 then you will receive the following values [0, 10, 20 … 100]
window - loads one ‘window’ of datapoints at a time containing “count” elements. Currently top and window are equivalent. There is work in progress to fully support a windowing setting.

Data Reduction Algorithm usage

DataReductionAlgorithm can be used in categorical, table or matrix dataview mapping.

For categorical data mapping it can be set into categories and/or group section of values.