Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Peter Globus]

I have a large OLAP cube, that I am looking to dynamically filter data on prior to loading into Power BI.

I want users to have the option of entering 1 or multiple items in the filter. To solve this, I created a parameter where users can enter comma delimited values. I then take this parameter and do the following using M and MDX:

1. Generate a list from the parameter string (Text.Split(myParameter,”,”)

2. Create the necessary MDX text surrounding parameter values to add as a filter.

3. Store the result in a string.

I then take the result string and add it to the query string at the correct area. If I create this string outside the query and enter the literal information everything works fine. If I use the string variable, I get the following error:

DataSource.Error: An error happened while reading data from the provider: ‘Object reference not set to an instance of an object.’ SQL Server OLAP

Is there a limitation for using M language in a Native Query when using OLAP, and if so, is there a workaround that will not be too painful to the end user?

——————————
Peter Globus
——————————

[Peter Globus]

After further testing, I confirmed the error I am seeing is unrelated to the string and OLAP. The actual cause is that no rows are returning for the items I initially included. Lesson learned. 🙂

——————————
Peter Globus
——————————
——————————————-

[Audrey Abbey]

Lesson SHARED and that is what is important. 

Error messages are so vague, lol.

——————————
Audrey Abbey
SR. BI Developer/Analyst
New Seasons Market
Portland OR
——————————
——————————————-