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

[Vannikannan04]

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.

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

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

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

[v-cgao-msft]

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

[Vannikannan04]

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