Forum Discussion

JonathanDavey1's avatar
JonathanDavey1
Helper I
4 years ago
Solved

Filter All Pages using Users Domain using "Measure" feature

Hi,

I would like to Filter BI-Reports using the Users Domain.

I am able to grab the User using Measure = Username()
I would have thought it simple to drag this Measure accross to Filter On All Pages and then add a simple if contains "DomainName" Filter my Table by Column  [ClientName].  My [ClientName] includes the Clients Domain.

I dont need or require ROW level Security, just the ability to Filter using a Viewers Domain.

 

Thank You Kindly,

Jonathan

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi JonathanDavey1 ,

     

    I have created a data sample and include the actual domain for "C".

     

    Please try to create a measure, and apply it to visual-filter pane, set as "is 1":

    Domain measure = 
var _pos=FIND("\", USERNAME())
var _domain=LEFT(USERNAME(),_pos-1)
return IF(CONTAINSSTRING(MAX('Table'[Include domains]),_domain),1,0)

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JonathanDavey1 ,

     

    I have created a data sample and include the actual domain for "C".

     

    Please try to create a measure, and apply it to visual-filter pane, set as "is 1":

    Domain measure = 
var _pos=FIND("\", USERNAME())
var _domain=LEFT(USERNAME(),_pos-1)
return IF(CONTAINSSTRING(MAX('Table'[Include domains]),_domain),1,0)

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JonathanDavey1's avatar
      JonathanDavey1
      Helper I
      4 years ago

      Hi Anonymous 

       

      Thank you really appreciate your help! I managed to enable the Domain Measure in the BI-Desktop and it worked in principle (USERNAME doesnt return domain only LOCAL account), so I published to BI-Online, now the filtered visual throws an error.

       

      Yours appreciatively

      Jonathan

       

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi JonathanDavey1 ,

     

    This error is because USERNAME() show different formats in Power BI Desktop and Power BI service:

    You can use username() within this expression. Be aware that username() has the format of DOMAIN\username within Power BI Desktop. Within the Power BI service and Power BI Report Server, it's in the format of the user's User Principal Name (UPN). Alternatively, you can use userprincipalname(), which always returns the user in the format of their user principal name, [email protected].
     
    So if you want to publish the report to service, I'd suggest you use USERPRINCIPLE() instead:
    Measure =
    var _remove=SUBSTITUTE(USERPRINCIPALNAME(),".com","")
    var _address=FIND("@", _remove)
    var _domain=Right(_remove,LEN(_remove)-_address)
    return IF(CONTAINSSTRING(MAX('Table'[Include domains]),_domain),1,0)

     

    Refer to:

    https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • JonathanDavey1's avatar
      JonathanDavey1
      Helper I
      4 years ago

      Hi, let me try again.


      I would like to Filter my Table using the Users Logged in Domain.

       

      Using "Measure = USERNAME"  I am able to 'get' the User's name & domain.

       

      Then I want to FILTER a Table using the Filters Contains Function. 

       

      The Table includes a Column containing the DOMAINs

       

      Many Thanks

      Jonathan