cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SarikaKumari18
Helper III
Helper III

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

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!
1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@SarikaKumari18 

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.

V-pazhen-msft_1-1609230416754.png

 

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.

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@SarikaKumari18 

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.

V-pazhen-msft_1-1609230416754.png

 

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.

SarikaKumari18
Helper III
Helper III

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.

@V-pazhen-msft : Request you to please help me to fix this

V-pazhen-msft
Community Support
Community Support

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

V-pazhen-msft_0-1609221904006.png

V-pazhen-msft_1-1609222058832.png

V-pazhen-msft_2-1609222299669.png

 

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

 

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

 

 

 

 

 

 

 

Thanks for your response @V-pazhen-msft .
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

@V-pazhen-msft  : 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) )

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!