Skip to main content

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​

  1. Connect Power BI to CDF.

  2. Select the drop-down button on the column header you want to add the filter to.

  3. Select Text Filters and the type of filter you want, for example, Begins with.

  4. In the Filter Rows dialog box, specify conditions to filter the rows in the column.

    Add Power BI filter

  5. Select Advanced to define many filters simultaneously or define and add them individually.

    tip

    Always specify the condition that filters the most rows first.

  6. The Cognite Power BI Connector sends the query to CDF for filtering, and Power Query displays the results returned from CDF.

    Power Query previews 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. CDF does the filtering, and no unnecessary data is transmitted between CDF and Power BI.

  • OData Service - the OData service can do local filtering to support functionality not provided by CDF, for example, functions like contains and endswith. First, reduce the data set as much as possible using CDF filtering.

  • Power Query - Power Query needs 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 OData service and CDF.

  • Local - local filtering done by the OData service before returning the result to Power BI.

  • FilterFunctions - Contains, StartsWith, and EndsWith.

  • Comparison - <, <=, >, >=, and =.

  • Search - does pushdown, but uses the search API and requires additional local filtering.

    info

    Search returns a limited number of rows, and you may not receive all matching data in CDF.

Assets​

PropertyPushdownLocal
Id==
ExternalId=, StartsWith=, FilterFunctions
Name=, Search=, FilterFunctions
DescriptionSearch=, FilterFunctions
ParentId==
ParentExternalId==
DataSetId==
Source==
MetaData=
RootId==
CreatedTimeComparisonComparison
LastUpdatedTimeComparisonComparison

Time series​

PropertyPushdownLocal
Id==
ExternalId=, StartsWith=, FilterFunctions
Name=, Search=, FilterFunctions
DescriptionSearch=, FilterFunctions
IsString==
MetaData=
Unit==
IsStep==
AssetId==
SecurityCategories
DataSetId==
CreatedTimeComparisonComparison
LastUpdatedTimeComparisonComparison

Data point​

PropertyPushdownLocal
Id==
TimeStampComparisonComparison

Events​

PropertyPushdownLocal
Id==
ExternalId=, StartsWithFilterFunctions
DescriptionSearch=, FilterFunctions
Source==
Type==
SubType==
MetaData=
DataSetId==
StartTimeComparisonComparison
EndTimeComparisonComparison
CreatedTimeComparisonComparison
LastUpdatedTimeComparisonComparison
AssetIdsContainsContains

Files​

PropertyPushdownLocal
Id==
ExternalId=, StartsWith=, FilterFunctions
Name=, Search=, FilterFunctions
Source==
MimeType==
MetaData
DataSetId==
SourceCreatedTimeComparisonComparison
SourceModifiedTimeComparisonComparison
CreatedTimeComparisonComparison
LastUpdatedTimeComparisonComparison
UploadedTimeComparisonComparison
Uploaded==
AssetIdsContainsContains

Sequences​

PropertyPushdownLocal
Id==
ExternalId=, StartsWith=, StartsWith
Name=, Search=, FilterFunctions
Description=, Search=, FilterFunctions
MetaData
AssetId==
DataSetId==
CreatedTimeComparisonComparison
LastUpdatedTimeComparisonComparison
Columns

CDF RAW databases​

PropertyPushdownLocal
Name==

CDF RAW tables​

PropertyPushdownLocal
Name==

CDF RAW rows​

PropertyPushdownLocal
Key==, FilterFunction
LastUpdatedTimeComparisonComparison

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:

  1. In Power BI, select Get Data in the Power BI main window, and select the Cognite Data Fusion connector.

  2. In the project name field, type in the project name, the resource, and the filter in this format:

    <project-name>/<resource-type>/?$filter=<Filter>

  3. 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.

Debugging

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.

Last updated