Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vannikannan04
Frequent Visitor

Measure working fine in Power BI Desktop Matrix same not working in report builder

Hi All,

Could you please help me in writing the measure.

I have a measure that works fine when used in the matrix of Power BI Desktop. However, when using the same measure in Report Builder, the expected results are not showing up. For example, when I use the measure in a matrix, I get the result as shown in the image below, where all the comments have been displayed as expected.

 

Vannikannan04_0-1708415731641.png

But when I use the same measure in a table, I only get one value from the measure. I understand the reason is that we are using the SWITCH function, so the measure is pulling and displaying the result of the first true condition

 

Vannikannan04_2-1708416228541.png

So we decided to create a separate measure in Power BI for every category and use it in Report Builder to display all the results as expected. However, the values are repeating. For example, 'comment1' is repeating in all the rows of the table, as shown in the image below. Is there a way to pick only the first row or any other option to pull the expected results into Report Builder

Vannikannan04_3-1708417183925.png

I have attached the measure below:

Comment = 
var leaf = SWITCH(TRUE(),
ISFILTERED(Account[Account]),CALCULATE(FIRSTNONBLANK(Account[Account],0),USERELATIONSHIP(Account[Account Id],'Financial Information - GL'[Account Id]),USERELATIONSHIP('GA - PL Mapping'[Cloud Account],'Account Hierarchy - EPBCS'[Account]),USERELATIONSHIP(Account[Account Id],'Account Hierarchy - EPBCS'[Account Id])),
"NA")

var fsli = SWITCH(TRUE(),
ISFILTERED('GA - PL Mapping'[Roll-up 1]),FIRSTNONBLANK('GA - PL Mapping'[Roll-up 1],0),
ISFILTERED('GA - PL Mapping'[Financial Statement Line]),FIRSTNONBLANK('GA - PL Mapping'[Financial Statement Line],0),
ISFILTERED('GA - PL Mapping'[FSLI H1]),FIRSTNONBLANK('GA - PL Mapping'[FSLI H1],0),
"NA")

--var select_date = SELECTEDVALUE('Calendar'[Period End Date])
var select_date = SELECTEDVALUE('Calendar'[Period End Date],0)
var key_ident = SELECTEDVALUE(Key_Identifier[Key_Identifier])
return
SWITCH(TRUE(),
ISFILTERED(Account[Account]),CALCULATE(CALCULATE(LASTNONBLANKVALUE(ENBVarianceCommentary[ID],MAX(ENBVarianceCommentary[Comment_Text])),REMOVEFILTERS('Calendar'),REMOVEFILTERS('GA - PL Mapping'),REMOVEFILTERS('Account Hierarchy - EPBCS'),REMOVEFILTERS('LOB Hierarchy - EPBCS'),USERELATIONSHIP(Account[Account Id],'Account Hierarchy - EPBCS'[Account Id]),ENBVarianceCommentary[Costcenter] = leaf,ENBVarianceCommentary[Account_Level] = "NA" && ENBVarianceCommentary[CostCenter_Level]="3",ENBVarianceCommentary[Account] = fsli,ENBVarianceCommentary[Date]=select_date,ENBVarianceCommentary[Period_Measure]="QTD",ENBVarianceCommentary[Key_Identifier]= key_ident)),
ISFILTERED('GA - PL Mapping'[Roll-up 1]),CALCULATE(CALCULATE(LASTNONBLANKVALUE(ENBVarianceCommentary[ID],MAX(ENBVarianceCommentary[Comment_Text])),REMOVEFILTERS('Calendar'),REMOVEFILTERS(Account),REMOVEFILTERS(EPBCS),REMOVEFILTERS('Account Hierarchy - EPBCS'),REMOVEFILTERS('LOB Hierarchy - EPBCS'),REMOVEFILTERS('Financial Information - GL'),USERELATIONSHIP('GA - PL Mapping'[Cloud Account],'Account Hierarchy - EPBCS'[Account]),ENBVarianceCommentary[Account] = fsli && ENBVarianceCommentary[Account_Level] = "NA" && ENBVarianceCommentary[CostCenter_Level] = "NA",ENBVarianceCommentary[Date] = select_date,ENBVarianceCommentary[Period_Measure]="QTD",ENBVarianceCommentary[Key_Identifier]= key_ident)),
ISFILTERED('GA - PL Mapping'[Financial Statement Line]),CALCULATE(CALCULATE(LASTNONBLANKVALUE(ENBVarianceCommentary[ID],MAX(ENBVarianceCommentary[Comment_Text])),REMOVEFILTERS('Calendar'),REMOVEFILTERS(Account),REMOVEFILTERS(EPBCS),REMOVEFILTERS('Account Hierarchy - EPBCS'),REMOVEFILTERS('LOB Hierarchy - EPBCS'),REMOVEFILTERS('Financial Information - GL'),USERELATIONSHIP('GA - PL Mapping'[Cloud Account],'Account Hierarchy - EPBCS'[Account]),ENBVarianceCommentary[Account] = fsli && ENBVarianceCommentary[Account_Level] = "NA" && ENBVarianceCommentary[CostCenter_Level] = "NA",ENBVarianceCommentary[Date] = select_date,ENBVarianceCommentary[Period_Measure]="QTD",ENBVarianceCommentary[Key_Identifier]= key_ident)),
ISFILTERED('GA - PL Mapping'[FSLI H1]),CALCULATE(CALCULATE(LASTNONBLANKVALUE(ENBVarianceCommentary[ID],MAX(ENBVarianceCommentary[Comment_Text])),REMOVEFILTERS('Calendar'),REMOVEFILTERS(Account),REMOVEFILTERS(EPBCS),REMOVEFILTERS('Account Hierarchy - EPBCS'),REMOVEFILTERS('LOB Hierarchy - EPBCS'),REMOVEFILTERS('Financial Information - GL'),USERELATIONSHIP('GA - PL Mapping'[Cloud Account],'Account Hierarchy - EPBCS'[Account]),ENBVarianceCommentary[Account] = fsli && ENBVarianceCommentary[Account_Level] = "NA" && ENBVarianceCommentary[CostCenter_Level] = "NA",ENBVarianceCommentary[Date] = select_date,ENBVarianceCommentary[Period_Measure]="QTD",ENBVarianceCommentary[Key_Identifier]=key_ident)),FALSE(),
"<br>"
)

Thanks

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @Vannikannan04 ,

Based on the measure you've shared, it seems like the core issue revolves around how the function is being utilized in conjunction with various filters and relationships in your data model. The function is indeed a powerful tool for conditional logic, but its behavior in Report Builder can be influenced by the context in which it's used, especially regarding row context and filter context.

Is it possible to find some differences in the overall model and calculation context?

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hi , 

Thanks for the reply, is there any other way to pull all the comment values in table format as the measure shown above doesn't display value in table visual of Power BI Desktop as well.

 

Thanks

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.