none
powerview filter dimension members RRS feed

  • Question

  • Hello,

    I have created a simple tabular model with 2 dimension tables and a Fact table .  In powerview I  select one column of a dimension and present in it in the view(for example the name of an employee). Then I want to filter this column by the second dimension the functionname. I drag the column of the second dimension to the filter pane and select 1 member (for example sales). After powerview refreshes this filter is not applied.

    To see what happens I changed the directquery mode of the model to ON  to trace the query. When I look into the query the filter is not applied (my filter doe not show up in het query).

    When I add a measure to the table, the filter is applied

    Why is this? do I miss something?





    • Edited by Gerben_S Friday, July 26, 2013 9:43 AM
    Friday, July 26, 2013 9:41 AM

Answers

  • Lets say you add Dimension1 attribute like Product to the Table, and Dimension 2 Attribute like Month on the Filter. Now if you select Month = Jan in the filter, product will not get filtered because there is no relationship between Product and Month. 

    However, if you add another attribute from Dimension1 like Category to the filter and then filter Category = Bikes, then the appropriate products will get filtered in the table. That is because Product and Category belong to the same dimension table.

    Another way filtering ill work is when you have measures which are connected to Dimension1 and Dimension2. The reason is that actually it is the measure which gets filtered by Date, and then the table just displays the non empty values of products for the filtered measure.

    Now to get back to what you said, you must have added 2 different dimensions on the table (like Product and Date). However, you must have used an attribute from Dimension1 or Dimension2 (eg Category from Dimension1). then it filters the table (actually, only filters the respective dimension). If you had added an attribute from Dimension3, this would not have happened.

    Can you confirm this behaviour? If not, can you please specify exactly what are the dimensions, attribute and filters? (pictures would also be really helpful)


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, August 15, 2013 1:24 PM
    Moderator

All replies

  • Hi Gerben,

    Thank you for your question. 

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Monday, July 29, 2013 6:40 AM
    Moderator
  • Hello,

    I just tested this in Excel 2013 connecting to Adventure Works Internet Sales tabular sample databases on Analysis Services 2012 SP1
    I used following steps in Excel after connecting the the tabular database:
    1) Added the Internet Total Sales and Product Category on the PowerView sheet
    2) Added Fiscal Year to the Filter pane
    3) Selecting one year on the filter -> the sheet is accordingly updated, no change to filter
    4) Right click on the PowerView sheet and selecting Refresh -> filter is still the same, the specific year is still selected

    If this is not how you see the issue, could you please discribe how I could do it? (would be helpful if you could try with Adventure Works sample database)
    Can you reprodcue the issue with the above steps?


    Thanks,
    Orsi
    Microsoft Online Community Support
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, August 14, 2013 2:33 PM
    Answerer
  • No, I am having the same problem as Gerben_S and the steps you outlined are not how to reproduce the problem. How to reproduce, assuming you have multiple dimensions tied to a fact table:

    1) Add any dimension column to the Power View sheet, from your example add only Product Category.

    2) Add any dimension to the filter section of Power View and filter on that dimension.

    3) The Product Category in the Power View sheet will not filter.

    The key is not to add any measures from the fact table to the Power View sheet. Once you do, the report will filter as expected.

    I am having the same problem and would love to know how to solve this.

    Thanks!!


    • Edited by MDigital1 Wednesday, August 14, 2013 8:43 PM
    Wednesday, August 14, 2013 8:35 PM
  • This is the way that tabular models work, and is not a problem with Power View.

    You will not be able to filter a dimension by another dimension, unless you add a measure to the context.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, August 15, 2013 2:15 AM
    Moderator
  • Hello Jason,

    this is not completely  true. Just like mdigital1 mentioned when I add just 1 dimension to the powerview sheet and add a dimension to the filter section sheet it will not filter. When I add 2 different dimensions to the powerview sheet (still no measures from the fact table) and add an other dimension to the filter, the filter works! So why doesn't it work with 1 dimension?

    Thanks

    Thursday, August 15, 2013 1:01 PM
  • Lets say you add Dimension1 attribute like Product to the Table, and Dimension 2 Attribute like Month on the Filter. Now if you select Month = Jan in the filter, product will not get filtered because there is no relationship between Product and Month. 

    However, if you add another attribute from Dimension1 like Category to the filter and then filter Category = Bikes, then the appropriate products will get filtered in the table. That is because Product and Category belong to the same dimension table.

    Another way filtering ill work is when you have measures which are connected to Dimension1 and Dimension2. The reason is that actually it is the measure which gets filtered by Date, and then the table just displays the non empty values of products for the filtered measure.

    Now to get back to what you said, you must have added 2 different dimensions on the table (like Product and Date). However, you must have used an attribute from Dimension1 or Dimension2 (eg Category from Dimension1). then it filters the table (actually, only filters the respective dimension). If you had added an attribute from Dimension3, this would not have happened.

    Can you confirm this behaviour? If not, can you please specify exactly what are the dimensions, attribute and filters? (pictures would also be really helpful)


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Thursday, August 15, 2013 1:24 PM
    Moderator
  • Hello Jason,

    let's say I have an customer last name in the powerview sheet. Now I want to know the customers who have bought the product, for example 'AWC logo cap' in the product dimension. When i put the productname in the filter section and i filter on the product name 'AWC logo cap' , the sheet is not filtered. Then i add an extra dimension to the powerview sheet. For example promotion name. Then the filter on product name is applied.

    I've added some pictures below..

    Regards Gerben

    Thursday, August 15, 2013 3:40 PM