topic Re: How to use measures in power bi report builder which was created in Power Bi dataset in Report Server

How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Mon, 28 Dec 2020 12:35:45 GMT

Hi All,
I have paginated report created already. As per new requirement, I need to use measures in report builder which was created in dataset pbix file.

Measure =IF(CONTAINS(employee,employee[ibm_name],USERPRINCIPALNAME(),employee[Anonymized data],"Yes"),"Anonymized",MAX(vw_Data_Extract_Report[CUSTOMERNAME]))
Report builder dataset properties uses below queries(not complete query as its too big but it uses variable and summarizecolumns with filter):
DEFINE
VAR vBusinessline = @BusinessLine
EVALUATE SUMMARIZECOLUMNS(
'vw_Data_Extract_Report'[CustomerName],
FILTER(VALUES ( 'vw_VOC_Data_Extract_Report'[businesslineid] ), 'vw_VOC_Data_Extract_Report'[businesslineid] = VALUE (vBusinessline) )

Requirement is to replace CustomerName field with measure created in dataset.
Request you all to please guide me to modify queries in report builder .

Any help would be much appreciated.
Thanks in advance!

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Tue, 29 Dec 2020 06:13:06 GMT

Anonymous
In order to use Measure in the dataset created in Power Bi Desktop, you must first publish the pbix to Power bi Serivce, then you can open report builder login with the account that has permission of the dataset.

Once logged in, you should able to connected power bi dataset and select the dataset you want to work with. In the query designer you should able to include the measure created in the datset.

You can check the doc for detail: Create a paginated report with a Power BI shared dataset - Power BI Report Builder - Power BI | Microsoft Docs

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Tue, 29 Dec 2020 06:52:42 GMT

Thanks for your response Anonymous .
I have published the dataset and I can see measures also in query designer.
I have got stuck in DAX query where I need to modify existing query.
EVALUATE SUMMARIZECOLUMNS(
'vw_Data_Extract_Report'[CustomerName],
FILTER(VALUES ( 'vw_VOC_Data_Extract_Report'[businesslineid] ), 'vw_VOC_Data_Extract_Report'[businesslineid] = VALUE (vBusinessline) )
If I just add measures in above query , it throws me an error stating column doesn't exist.
Looks like need to modify the dax fourmula.
I can't use copy query (from performance analyzer) as it is different from report builder(we have used variable and filter).

Please guide me how can I modify existing query to include measures.
Thank you in advance

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Tue, 29 Dec 2020 07:06:56 GMT

Anonymous : I have tried the below query as well but no luck
DEFINE
VAR vBusinessline = @BusinessLine
EVALUATE SUMMARIZECOLUMNS(
ROLLUPGROUP(
'vw_Data_Extract_Report'[CustomerName]
),
"AnonymizedCustomerName", 'vw_VOC_Data_Extract_Report'[AnonymizedCustomerName],
FILTER(VALUES ( 'vw_Data_Extract_Report'[businesslineid] ), 'vw_Data_Extract_Report'[businesslineid] = VALUE (vBusinessline) )

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Tue, 29 Dec 2020 14:47:49 GMT

The below query is working where AnonymizedCustomerName is measure but when I add filter expression to the below query which in turn uses parameter its throwing error

DEFINE
VAR test =
    SUMMARIZECOLUMNS(
      'vw_VOC_Data_Extract_Report'[BusinessLine],
      'vw_VOC_Data_Extract_Report'[CUSTOMERNAME],
      "AnonymizedCustomerName", 'vw_VOC_Data_Extract_Report'[AnonymizedCustomerName]
    )
EVALUATE
test

-------------------------------------------------------------------------------
My requirement is something like below which is actually not working
DEFINE
VAR vBusinessline = @BusinessLine
VAR test =
SUMMARIZECOLUMNS(
'vw_VOC_Data_Extract_Report'[BusinessLine],
'vw_VOC_Data_Extract_Report'[CUSTOMERNAME],
"AnonymizedCustomerName", 'vw_VOC_Data_Extract_Report'[AnonymizedCustomerName],
FILTER(VALUES ( 'vw_VOC_Data_Extract_Report'[businesslineid] ),
'vw_VOC_Data_Extract_Report'[businesslineid] = VALUE (vBusinessline) )
)
EVALUATE
test

Request you all to please help and guide me.

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Tue, 29 Dec 2020 14:49:48 GMT

Anonymous : Request you to please help me to fix this

Re: How to use measures in power bi report builder which was created in Power Bi dataset

Anonymous — Mon, 11 Jan 2021 07:12:40 GMT

Anonymous

Check the used column in the measure, make sure you have add the measure and all refer columns into the query to create a new dataset in RB.

Seems [AnonymizedCustomerName] is a measure, so you should add a calculate() function to call out the filter.

"AnonymizedCustomerName", Calculate('vw_VOC_Data_Extract_Report'[AnonymizedCustomerName],
FILTER(VALUES ( 'vw_VOC_Data_Extract_Report'[businesslineid] ),
'vw_VOC_Data_Extract_Report'[businesslineid] = VALUE (vBusinessline) )
))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.