Forum Discussion

RYA's avatar
RYA
New Member
3 years ago

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

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

 

Need Help
Tips and Tricks
Tutorial Requests

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      RYA
      New Member
      3 years ago

      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

  • Gengar's avatar
    Gengar
    Resolver I
    3 years ago

    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

  • RYA's avatar
    RYA
    New Member
    3 years ago

    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