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...
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

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