Unable to filter visual in PBI report using BW Query Direct Query mode

[Anonymous]

Hi,

I am using a BW Query in direct query mode to create a dashboard in PBI. I am using the latest version of PBI Desktop and SAP .NET Connector needed for implementation 2.0 as suggested by Microsoft.

The query is based on a composite provider which is built on calculation views based on ADSO. Some of the flags (dimensions I need to filter) are set in the calculation views in our BW/4HANA system.

I am able to run the query without issues and generate the visuals I need. However, the requirement is to filter a few visuals by the flags (dimensions). When I add those to filter shelf, either visual or page level, I don't see the values to set the filter. 

In the image below are the valid values for the Invoice Lock dimension.

I would like to filter out "NA" for invoice locks but the filter is not showing any values.

Please can you help?

Thanks,

@Anonymous

[Tad17]

Hey @Anonymous 

Do the values appear if you put them in a slicer visual? Also, can you use any other the other filtering options other than basic filtering?

[Anonymous]

Hi @Tad17 

The values don't appear in slicer either and I have tried advanced filtering to no avail.

[Tad17]

Hey @Anonymous 

My first thought is to check and make sure there is nothing filtering the visual (you can do this by clicking on the funnel icon next to the slicer visual). I do not expect this to be the case since they do not show in the filter pane, but its always good to check.

Second, make sure there is no Row-Level-Security enacted in the original data or in the query. I also do nto expect this to be the case since you can see it in the table.

Lastly, there are some known issues with SAP/BW direct querries. check the links below for more information:

https://community.powerbi.com/t5/Desktop/SAP-BW-Attributes-not-showing-up-in-Power-BI-SAP-Business/td-p/50930

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-sap-bw

Let me know if any of that helps.

Sorry I'm very familiar with the BW query connector.

[Anonymous · Accepted solution]

@Tad17 

Looks like BW to PBI integration is not really on par with other systems. In any case, I was able to filter as required.

I ran the query for the duration (2 years) where it showed all possible values for the flags. Then, on the visual, I CTRL+clicked each of the values I wanted and "included" them. Excluding flags doesn't work.

Now, I am able to filter as required. 

This looks like a workaround but will be difficult when the list of flags to be included is long.

BW Filter Workaround