Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 fil...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.

    parameterize connection 

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

     

    Thanks 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.