Forum Discussion
Advocate VCube 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
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 )
4 Replies
v-piga-msftResident 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
- ImkeF
Community Champion
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 )
karoImkeF , 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
- v-piga-msft
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