Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Default Member set to USERNAME() on Tabular model

I have PBIX file with data import model. The report page XXX needs to defualt to a value in the User table based on USERNAME(). However, the user must be able to view all data for other users as well, by manually clearing the default filter on the User table.

 

This feature has been brought up a few times, but I want to hear from people who have managed to solve this in a pretty way :)

 

Thanks.

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    By Tabular model I just meant an import data source. I managed to solve this in a satisfactory way though. I made a Yes/No parameter table and added a dropdown slicer filtered to "Yes" to the report, I then Created two measures, one with no filter, and one filtered to USERNAME(), then created this measure to dynamically switch between the two measures, depending on the value in the parameter-table: 

     

    IF (
    HASONEVALUE ( IsCurrentUser[CurrentUser] ); // My Parameter Table
    SWITCH (
        VALUES ( IsCurrentUser[CurrentUser] ); // My Non-filtered SalesQty
        "Yes"; SalesFact[SalesQty(CurrentUser)]; // My USERNAME() filtered SalesQty
        "No"; SalesFact[SalesQty(AllUsers)]
    );
    SalesFact[SalesQty(AllUsers)]
)

    Now the report will default to the current user, but he can clear the filter on the parameter dropdown slicer to see all users.

     

    It was actually alot more simple than first expected, as I had a little time to get around it :)

     

    Thanks for input though.

     

    RSK

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

    If my understanding is correct, you import Tabular model to Power BI, and you set USERNAME() in the model. "The report page XXX needs to defualt to a value in the User table based on USERNAME()." means userA can only see userA's value and userB can only see userB's value in report page. All the settings were done in the tabular model, so we can not let the userA see all data. Because we can not set it in Power BI.

    Best Regards,
    Angelia

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      By Tabular model I just meant an import data source. I managed to solve this in a satisfactory way though. I made a Yes/No parameter table and added a dropdown slicer filtered to "Yes" to the report, I then Created two measures, one with no filter, and one filtered to USERNAME(), then created this measure to dynamically switch between the two measures, depending on the value in the parameter-table: 

       

      IF (
    HASONEVALUE ( IsCurrentUser[CurrentUser] ); // My Parameter Table
    SWITCH (
        VALUES ( IsCurrentUser[CurrentUser] ); // My Non-filtered SalesQty
        "Yes"; SalesFact[SalesQty(CurrentUser)]; // My USERNAME() filtered SalesQty
        "No"; SalesFact[SalesQty(AllUsers)]
    );
    SalesFact[SalesQty(AllUsers)]
)

      Now the report will default to the current user, but he can clear the filter on the parameter dropdown slicer to see all users.

       

      It was actually alot more simple than first expected, as I had a little time to get around it :)

       

      Thanks for input though.

       

      RSK