locked
Filter a column without creating join in SSDT RRS feed

  • Question

  • I have two tables, one uservendor relationship table and other ProductVendor table.

     

    What I want is when I login as a user, Vendor should be defaulted to my own Vendor from Uservendor table.

    And the correspondng Product slicer should have list of all products tagged to my Vendor.

    However I don't want to create join between these two tables as it will filter out the other vendors data which I dont want.

     

    Because along with that I also have one more slicer which should give me list of other vendor's product.

     

    Basically I need to filter a column without creating join. 

    Friday, April 6, 2018 9:21 AM

All replies

  • To do this in a tabular model you are going to have to create 2 versions of each measure. One that returns an amount for all Vendors, and a second which calculates the amount for the "default" vendor. The unfiltered version of the measure would be where you would implement your business logic, then your filtered version would just lookup the current user in the UserVendor table and filter the measure for the relevant vendor. (you may want to come up with a better naming convention than filtered/unfiltered, this is just to illustrate the concept)

    eg.

    UnFiltered Amount:=SUM( [Amount] ) 

    Filtered Amount:=CALCULATE( [UnFiltered Amount]
       , FILTER(ProductVendor
             , ProductVendor[Vendor] = LOOKUPVALUE(UserVendor[Vendor], UserVendor[UserId], USERNAME()
       )
    )



    http://darren.gosbell.com - please mark correct answers

    Monday, April 9, 2018 1:14 AM
  • Hi akj2784,

    Thanks for your question.

    According to your description, what you want to achieve is dynamic row level security with Analysis services tabular model. For more information, please refer to below tutorial:
    https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 9, 2018 7:59 AM