Filter CDF data in Power BI
Add filters in Power BI to filter large data sets in milliseconds instead of minutes or hours.
Cognite Data Fusion (CDF) projects can contain millions of rows of data, and downloading them all into Power BI isn't efficient or feasible.
Filtering is vital to create workable data sets in Power BI. The Cognite Power BI Connector pushes the filters to CDF via the Cognite OData Service. CDF performs the filtering and returns only the matching rows to Power BI.
Add filters in the Power Query Editor
Select the drop-down button on the column header you want to add the filter to.
Select Text Filters and the type of filter you want, for example, Begins with.
In the Filter Rows dialog box, specify conditions to filter the rows in the column.
Select Advanced to define many filters simultaneously or define and add them individually.
tipsAlways specify the condition that filters the most rows first.
The
Cognite Power BI Connectorsends the query toCDFfor filtering, andPower Querydisplays the results returned fromCDF.Power Querypreviews the result set, usually 1000 rows. The final filtering happens when you select Close and apply.
Supported filtering for CDF resource types
The Cognite Power BI Connector can fold or push filters to CDF via the Cognite OData Service. The filtering can happen in:
CDF- the most effective filtering.CDFdoes the filtering, and no unnecessary data is transmitted betweenCDFandPower BI.OData Service- theODataservice can do local filtering to support functionality not provided byCDF, for example, functions likecontainsandendswith. First, reduce the data set as much as possible usingCDFfiltering.Power Query-Power Queryneeds to retrieve all the data to do the filtering. For large tables, this may involve significant amounts of data. Reduce the data set as much as possible first.
The tables below outline what filtering is supported for each resource type in CDF, which filters will be pushed down to CDF, and what filtering will be done locally by the OData Service.
Definitions
Pushdown - filter functions that are pushed down to the
ODataservice andCDF.Local - local filtering done by the
ODataservice before returning the result toPower BI.FilterFunctions -
Contains,StartsWith, andEndsWith.Comparison -
<,<=,>,>=, and=.Search - does
pushdown, but uses the search API and requires additional local filtering.informasjonSearch returns a limited number of rows, and you may not receive all matching data in
CDF.
Assets
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
ExternalId | =, StartsWith | =, FilterFunctions |
Name | =, Search | =, FilterFunctions |
Description | Search | =, FilterFunctions |
ParentId | = | = |
ParentExternalId | = | = |
DataSetId | = | = |
Source | = | = |
MetaData | = | |
RootId | = | = |
CreatedTime | Comparison | Comparison |
LastUpdatedTime | Comparison | Comparison |
Time series
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
ExternalId | =, StartsWith | =, FilterFunctions |
Name | =, Search | =, FilterFunctions |
Description | Search | =, FilterFunctions |
IsString | = | = |
MetaData | = | |
Unit | = | = |
IsStep | = | = |
AssetId | = | = |
SecurityCategories | ||
DataSetId | = | = |
CreatedTime | Comparison | Comparison |
LastUpdatedTime | Comparison | Comparison |
Data point
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
TimeStamp | Comparison | Comparison |
Events
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
ExternalId | =, StartsWith | FilterFunctions |
Description | Search | =, FilterFunctions |
Source | = | = |
Type | = | = |
SubType | = | = |
MetaData | = | |
DataSetId | = | = |
StartTime | Comparison | Comparison |
EndTime | Comparison | Comparison |
CreatedTime | Comparison | Comparison |
LastUpdatedTime | Comparison | Comparison |
AssetIds | Contains | Contains |
Files
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
ExternalId | =, StartsWith | =, FilterFunctions |
Name | =, Search | =, FilterFunctions |
Source | = | = |
MimeType | = | = |
MetaData | ||
DataSetId | = | = |
SourceCreatedTime | Comparison | Comparison |
SourceModifiedTime | Comparison | Comparison |
CreatedTime | Comparison | Comparison |
LastUpdatedTime | Comparison | Comparison |
UploadedTime | Comparison | Comparison |
Uploaded | = | = |
AssetIds | Contains | Contains |
Sequences
| Property | Pushdown | Local |
|---|---|---|
Id | = | = |
ExternalId | =, StartsWith | =, StartsWith |
Name | =, Search | =, FilterFunctions |
Description | =, Search | =, FilterFunctions |
MetaData | ||
AssetId | = | = |
DataSetId | = | = |
CreatedTime | Comparison | Comparison |
LastUpdatedTime | Comparison | Comparison |
Columns |
CDF RAW databases
| Property | Pushdown | Local |
|---|---|---|
Name | = | = |
CDF RAW tables
| Property | Pushdown | Local |
|---|---|---|
Name | = | = |
CDF RAW rows
| Property | Pushdown | Local |
|---|---|---|
Key | = | =, FilterFunction |
LastUpdatedTime | Comparison | Comparison |
Use custom queries to filter data
You can use the Cognite Power BI connector to create custom queries to filter properties. For example, to filter on Asset Metadata properties:
In
Power BI, select Get Data in thePower BImain window, and select the Cognite Data Fusion connector.In the project name field, type in the project name, the resource, and the filter in this format:
<project-name>/<resource-type>/?$filter=<Filter>Select OK, and then Transform Data to open a query preview.
Examples
Filtering follows the OData specification, and you can create multi-attribute filters by chaining filters with and/or terms. For example, an equality filter uses the AttributeName eq 'AttributeValue' format:
Name eq '23-TE-96116-04'
Metadata filtering
For metadata, filtering is performed on properties nested inside the metadata object. To filter on a metadata property, you need to create a filter in the form of "MetaData/NestedAttributeName eq 'NestedAttributeValue'", for example:
MetaData/RES_ID eq '525283'
This is an example of a custom query to the publicdata project, filtering on Metadata/RES_ID:
publicdata/Assets/?$filter=MetaData/RES_ID eq '525283'&tenantID=48d5043c-cf70-4c49-881c-c638f5796997
This is an AND filter on Metadata/RES_ID and Name:
publicdata/Assets/?$filter=MetaData/RES_ID eq '525283' and Name eq '23-TE-96116-04'&tenantID=48d5043c-cf70-4c49-881c-c638f5796997
In the examples above, tenantID=48d5043c-cf70-4c49-881c-c638f5796997 is the Microsoft Entra ID tenant configured for the publicdata project.
To debug filtering, it's helpful to use tools that lets you see what filters is pushed down to the OData service, for example Fiddler.