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

[Anonymous]

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!

[Anonymous · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

@Anonymous : Request you to please help me to fix this

[Anonymous]

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

[Anonymous]

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

[Anonymous]

@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) )