Interaction between CXO and Power BI filters
- Rachel Thomas
When a Power BI table is embedded with a CXO table, the integrated report looks like the example below:
On the right hand side is the Power BI table, on the left hand side is the CXO table.
If the table structure in Power BI is correctly set up, you can filter the Power BI data from CXO using the filtering options like the POV changes, the listen to functionality in tables, item selectors and so on. There are two types of data relationships between CXO and Power BI data: 1:1 and 1:many. These are shown below using the listen to functionality in CXO tables.
Note: Power BI data is filtered through CXO, but you cannot filter CXO data by changing the Power BI filters.
One to one (1:1) filtering
The Power BI listens to the chosen CXO filters and returns one matching member:
Example: Click the Listen To button in the CXO table for East Sales, and the Power BI table shown on the right will show only East Sales data:
It is recommended to create a Power BI table structure using CXO model to match member names easily. Also refer matching tables within CXO in case of different dimension member names.
One to many (1:m) filtering
Example: After choosing the entity Electronic Stores in the CXO table, the Power BI table displays all the children under the parent entity. In this example, the Power BI children are equivalent to those in the CXO metadata. However, this type of filtering also works if the child is not in the CXO data source:
For this, it is necessary to create a Power BI table structure with the right parent-child relationships. You can then match the parent members between CXO and Power BI, possible using the embedding match functionality.
Refer:
Embedding Microsoft Power BI in CXO
Smart Table Structure for Embedding Power BI