How to create User prompt for only querying a specific record/customer from DB

[RYA]

Hi,

I have a usecase where I have to build a PowerBI customers report for several PowerBI endusers.
The report is containing:
Customer number, Customer Name, Account number, Account value, Account status, Date

First requirement is: When the end users open/start the report they must be met with a prompt where they enter a customer number and a date to query directly against our customer database on MSsql. When the report is open, they must be able to refresh the data set with the new/next customer number and date. Which ensures that that the dataset in the report only contains information related to searched customer and date.

Second requirement is: The end users should be able to use the reports at the same time.

Third requirement is: It should be published reports.

Is it possible to do in PowerBI and/or other PowerBI related tools?

Regards RYA

[v-yiruan-msft]

Hi @RYA ,

You can refer the following links to get it by using Power Query Parameter:

Include Parameters within Power BI to Query a SQL Data Source

(x as text, y as text)=>let
    Source = Sql.Database("x", "y", [Query="select *#(lf)from z#(lf)WHERE [period_number]="&P2&"#(lf)AND [fiscal_year]="&P1&""])
in
    Source

PS: You can create a parameter to filter the customer...

Using Query Parameters in Filter Rows

Create parameterFilter the rows by the parameter

Best Regards

[RYA]

Hi Team_Rena

Im not sure that the solution can be used for published reports. At least i can't get a pop-up prompt where the end user has to make there input parameters which are Customer no. and date.

/Recep

[RYA]

Hi @Gengar 

RLS is not an option as I see it.
The end user is only allowed access to data related to the specific customer for a specific task she/he is working on at a given time.
If we only restrict by RLS she has all customers and there accounts in the dataset at all time, and will not restrict for a specific customer dynamicly.

Regards RYA

[Gengar]

Hi @RYA ,

Maybe you can use RLS?

Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn't restrict this data access.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. For Analysis Services or Azure Analysis Services lives connections, you configure Row-level security in the model, not in Power BI Desktop. The security option will not show up for live connection datasets.

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

Hope it can help you!

Ganger