Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Report Header using DAX

I am hoping this a simple one that I am over thinking...

I have a report that makes use of some dynamic RLS which uses an access table built in to the report that looks like this:

User_EmailUserNameAgentNumCompanyRegionAccess Required
      

 

So what I created a measure that filters the report title to the 'Company' name for a specific user when they log in using the below:

 

Page_Title = IFERROR(IF(CONTAINS(Access_Table,Access_Table[User_Email],USERPRINCIPALNAME())=TRUE(),LOOKUPVALUE(Access_Table[Comapny],Access_Table[User_Email],USERPRINCIPALNAME()),""),"Multiple Selections")

This works fine for 99% of cases, but I have one scenario when there are a number of users who have access to two companies and do not want to see "Multiple Selections" at the top of the report, they want to see the company name.

Example
 
Company
Apple
Apple - Genius Bar


In this scenrio they would want to see 'Apple' at the top. Anybody able to help?

Need Help
Tips and Tricks
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can try to meet your requirement use following formula:

     

    Page_Title =
VAR t =
    FILTER ( Access_Table, [User_Email] = USERPRINCIPALNAME () )
VAR CompanyNumber =
    COUNTROWS ( t )
RETURN
    IF (
        CompanyNumber = 0,
        "",
        IF (
            CompanyNumber = 1,
            MAXX ( t, [Company] ),
            MAXX ( TOPN ( 1, t, [Company], ASC ), [Company] )
        )
    )

     

    Or

     

    Page_Title =
VAR t =
    FILTER ( Access_Table, [User_Email] = USERPRINCIPALNAME () )
VAR CompanyNumber =
    COUNTROWS ( t )
RETURN
    IF ( CompanyNumber = 0, "", MAXX ( TOPN ( 1, t, [Company], ASC ), [Company] ) )

     

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can try to meet your requirement use following formula:

     

    Page_Title =
VAR t =
    FILTER ( Access_Table, [User_Email] = USERPRINCIPALNAME () )
VAR CompanyNumber =
    COUNTROWS ( t )
RETURN
    IF (
        CompanyNumber = 0,
        "",
        IF (
            CompanyNumber = 1,
            MAXX ( t, [Company] ),
            MAXX ( TOPN ( 1, t, [Company], ASC ), [Company] )
        )
    )

     

    Or

     

    Page_Title =
VAR t =
    FILTER ( Access_Table, [User_Email] = USERPRINCIPALNAME () )
VAR CompanyNumber =
    COUNTROWS ( t )
RETURN
    IF ( CompanyNumber = 0, "", MAXX ( TOPN ( 1, t, [Company], ASC ), [Company] ) )

     

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.