Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karo
Post Patron
Post Patron

Cube data- dynamic query selection based on a list of values ?

Hi,

 I am wonder if there is a way to apply a dynamic query filter based on a list to reduce the scope of the data when we are using connection to Analyses Services - Cube data without lossing an option to use Cube Tools ?

 

example: I have a list of Product IDs and this list can change month over month. I would like to automatically reduce the amount of the data in my model (also to avoid Locking conflicts) , however I do not have any other dimension what can be used in this case. The filtering currently in scope Product IDs at the end is not an option due to the volume of the data. Also I would like to avoid manual changes in PQ every time when the list will change.

 

Any hints?

 

Regards,

Karo

1 ACCEPTED SOLUTION

If you apply the filter as the first step, the query should still fold back to the server even without writing dedicated code.

But if for some reason this doesn't work, you can add a query record with a specific filtering command (https://community.powerbi.com/t5/Desktop/Passing-parameters-to-MDX-source-queries/td-p/98255 or https://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503 )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @karo,

Have you solved your problem based on the suggestion of ImkeF?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you s till need help, please feel free to ask.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @karo ,

If you use live connection mode, i have no good idea for your requirement.

If you use import mode, I'm afraid that you could try query parameter in Query Eidtor to filter the data.

Hope @Greg_Deckler and @ImkeF have other ideas.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

If you apply the filter as the first step, the query should still fold back to the server even without writing dedicated code.

But if for some reason this doesn't work, you can add a query record with a specific filtering command (https://community.powerbi.com/t5/Desktop/Passing-parameters-to-MDX-source-queries/td-p/98255 or https://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503 )

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF , @v-piga-msft ,

Thank you so much for all the hints ! I have the solution with  List.Contains as a first step in blank query 🙂

Big thank you!,

Karo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.