Using a parameter from semantic model to PowerBI Report Builder

[CBI]

Hello,

I'm new using PowerBI, and i'm working on a new migration project, i hope you can help me.

I have a query where i did put some parameters in a semantic model which one will be shared into many reports, i have 3 questions about it :
- For example, the parameter "GroupeFiltre" used in the query below : is it possible to allow the end user to enter it manually somewhere when refreshing the report on PowerBI ?
- The same thing is possible on PowerBI Report builder with that semantic model created on PowerBI ? 

- If i use a semantic model with DirectQuery shared on several reports, is there any impact if i refresh a report ? Will my report get the datas filtered with the parameter set in point 1 or point 2 from my datasource correctly ? And does my 2nd report will have his own datas with "GroupeFiltre" set with an other value ?

The objective is to request with a maximum of parameters to decrease the results of the query. I'm just looking for a mutual semantic model that would be used to refresh the queries with a minimum cost. The reports already exists on SSRS with shared datasets.

Just for your information, here is my M query :

let
Source = Sql.Database("xxxxxxxxxxxxxxxx", "xxxxxxxxxxxxxxxxx", [Query="SELECT DISTINCT E.PrimaryKey
,E.Nom
,E.Prenom
,CASE
WHEN " & GroupeFiltre &"< 99
THEN E.Nom + ' ' + E.Prenom
WHEN " & GroupeFiltre & " = 99
THEN E.Nom + ' ' + E.Prenom + ' (' + E.Matricule + ')'
END AS NomAffiche
,E.SortValue
,E.TypeDeRessource
FROM db_po.Employe AS E
INNER JOIN db_po.RessourceGroupeDePlanification AS RGP ON RGP.Ressource = E.PrimaryKey
INNER JOIN dbo.RFParamCalendrier AS RFPC ON RGP.DateDeFin > RFPC.CalDate
AND RGP.DateDeDebut <= RFPC.CalDate
WHERE E.Prenom != '' -- on veut exclure les employés virtuels
AND E.Archive = 'F'
--AND E.TypeDeRessource IN (TypeDeRessourcePL)
AND (
(
" & GroupeFiltre & " < 99
AND RFPC.CalWeekYear = "&Annee&"
AND RFPC.CalWeek = "&SemaineNo&"
)
OR "&GroupeFiltre&" = 99
AND (
E.Nom like '"&YEAR&"' + '%'
OR E.Matricule like '"&EmployeFiltre&"' + '%'
)
)"])
in
Source

Thanks in advance for your help ! 🙂

[CBI]

Thanks a lot @v-linyulu-msft !!

[v-linyulu-msft · Accepted solution]

Hi, @CBI 

1.Firstly, for PBIX reports, you can modify the parameter values in the semantic model before refreshing the report. If these parameters are used in M code, they will affect the final data refresh.

For more details, please refer to:

Report parameters in Power BI Report Builder - Power BI | Microsoft Learn

Edit parameter settings in the Power BI service - Power BI | Microsoft Learn

2.Secondly, regarding parameter settings in Power BI Report Builder:

Paginated reports work slightly differently from Power BI Desktop. When displaying data in paginated reports, if query parameters are set, users must manually enter or select parameter values first. The system will then query and display the filtered report results in real-time. Therefore, users can manually enter parameter values as filter conditions before displaying the report.

For paginated reports sharing the same semantic model, you can set different query parameters for each paginated report (query parameters for paginated reports are independent of each other).

If multiple paginated reports use the same semantic model as the data source, you can create different query parameters for these semantic models separately, and the paginated reports will not affect each other.

As shown in the image:

Users must select the query parameter values and click "View Report" to display the queried paginated report.It can be understood that the data in paginated reports is queried in real-time from the data source (with user-entered parameters as filter conditions).

 

If the user does not assign values to the query parameters, the paginated report cannot be queried.

3.Finally, for multiple PBIX files using the same semantic model (not paginated reports):

The parameters set are included in the semantic model. Therefore, multiple PBIX files created using the same semantic model will have the same parameters when refreshed, affecting each other. If you set a parameter value to "data1" in report A, other reports using the same semantic model will also use the parameter value "data1" when querying/refreshing data.

For example, if I set a parameter representing the database as DatabaseName in the semantic model, changing this parameter value will affect all PBIX files using this semantic model as the data source when refreshed (for PBIX reports, sharing a semantic model means sharing Power Query parameters).

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sup3rc0c0]

Hi,

Is somebody has any idea please ?