Forum Discussion
Need help to create filtered measures when I cannot access data source/query editor
Hi all, I am trying to create a report where I do not have access to the data source as the company keeps the data source private. I am also unable to add any additional sources into the PowerBI file (the company has already created a cloud-linked blank template to all the sales data which is what I am using).
I am trying to create a list of independent market segments to analyze the sales data, for example, 50% of Product A, 20% of Product B, 10% of Product C sales go into my newly-created e-Commerce segment, while 10% of Product B, 3% of Product D, etc go into another Construction segment. How am I able to do this if I cannot access, edit or amend the data source nor access the query editor?
Currently, I can only creating quick measures of filtered Sales of Product A, Product B, Product C, Product D (4 separate quick measures) and then I manually create a measure to add the filtered sales multiplied by the percentage of their contribution to find the market segment sales.
Is there a better way? I can't keep doing it manually cause I have about 100+ products. Thanks all in advance.
Hi Anonymous,
I think you need to ask for some dummy data from the DBA.(you can ask him to copy some of records with the similar data structures) It is really hard to coding formula without any same data and data structures.
First, you can create a query parameter with the source data from the product column ('add as new query' feature). Then you can add a filter on the product column with the query parameters. ('suggest value' setting to 'query' and potin to the 'product list' that created in above steps as the source)
After these steps, you can edit parameters on the data view side(pick a product from the product list) to dynamic change the filter results based on product parameters.
Deep Dive into Query Parameters and Power BI Templates
Finally, you can change the 'source' step on the 'query edit' side to use query parameters parameterized the connector string with a connector to point the real data source.
If you only want to have a measure that calculates with slicer selections, you can try to use measure with calculating and allselected functions to get the filer effect from the slicer.
Measure = CALCULATE ( 'Expression', ALLSELECTED ( 'RawTable' ), VALUES ( 'Selector'[Columns] ) )Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
DAX – The Many Faces of VALUES()
Regards,
Xiaoxin Sheng
3 Replies
- amitchandak
Super User
Anonymous ,Not very clear
Can you share sample data and sample output in table format?
SEGMENTATION
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationThanks for the quick response. As mentioned, I have no access to the underlying data source. Hence, I am unable to show any sample data. I have a PowerBI file connected to cloud data and a list of measures which are pre-created by the company. I am able to use those measures to create visuals.
However, I need to manipulate the underlying data to perform certain calculations. Eg. I want to create a new variable with 50% sales from Product A, 20% sales from Product B, etc, and I cannot find out how to do so in my desktop Power BI. I can create new filtered measures, etc but I am trying to see if there is a better way to do the example I mentioned above (create a new variable with 50% sales from Product A, 20% sales from Product B, etc). Do note that the sales are combined number and in order to calculate sales of Product A, I would have to create a filtered measure of Sales data filtered by Product A only.
Hence, if I have 100 products, I have to create 100 filtered measures and then in order to create my variable, I have to create a measure which adds certain filtered measures together. I cannot think of any other way as I have no access to underlying data set or to the query editor.
Hope somebody can help, thanks.
Hi Anonymous,
I think you need to ask for some dummy data from the DBA.(you can ask him to copy some of records with the similar data structures) It is really hard to coding formula without any same data and data structures.
First, you can create a query parameter with the source data from the product column ('add as new query' feature). Then you can add a filter on the product column with the query parameters. ('suggest value' setting to 'query' and potin to the 'product list' that created in above steps as the source)
After these steps, you can edit parameters on the data view side(pick a product from the product list) to dynamic change the filter results based on product parameters.
Deep Dive into Query Parameters and Power BI Templates
Finally, you can change the 'source' step on the 'query edit' side to use query parameters parameterized the connector string with a connector to point the real data source.
If you only want to have a measure that calculates with slicer selections, you can try to use measure with calculating and allselected functions to get the filer effect from the slicer.
Measure = CALCULATE ( 'Expression', ALLSELECTED ( 'RawTable' ), VALUES ( 'Selector'[Columns] ) )Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
DAX – The Many Faces of VALUES()
Regards,
Xiaoxin Sheng
