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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Showing Text and Currency format in same column for Power BI Report Builder

Hello,

 

I have a Power BI Report Builder Report I'm working on with a tablix object that shows questions and answers from a survey (I'm pulling in a Power BI dataset). My data model has two tables that looks like this:

"Question" Table

QuestionQuestionIDAnswerType
How much money?Q01Currency
How's it Going?Q02Multi-line Text

 

"Answers" Table

TitleAnswerAnswersQuestionID
Survey1$504Q01
Survey1Great!Q02
Survey2$22Q01
Survey2Pretty WellQ02

 

Everything is working fine, except I'm trying to get the "Answer" column's Value property to be dynamic based on the related "answer type" column that I have. Here's my formula:

 

 

 

=Switch(
	Lookup(Fields!AnswersQuestionID.Value, Fields!QuestionID.Value, Fields!AnswerType.Value, "Questions") = "Multi-line Text",Fields!Answer.Value,
	Lookup(Fields!AnswersQuestionID.Value, Fields!QuestionID.Value, Fields!AnswerType.Value, "Questions") = "Yes/No",Fields!Answer.Value,
	Lookup(Fields!AnswersQuestionID.Value, Fields!QuestionID.Value, Fields!AnswerType.Value, "Questions") = "Currency",FormatCurrency(Fields!Answer.Value,2),
	Lookup(Fields!AnswersQuestionID.Value, Fields!QuestionID.Value, Fields!AnswerType.Value, "Questions") = "Whole Number",FormatNumber(Fields!Answer.Value,0)
)

 

 

 

 

 

 

When I run the report I get errors for the answers that should be plain text:

AnswerErrorScreenshot.png

 

 

 

 

 

 

 

 

I've also tried a similar formula on the Format property without any luck. I'd think this should be possible since everything is still a string type, but I'm guessing there's some type mismatch issue going on that I'm not understanding. I also don't know how to inspect the rendered "#Error" for more debugging guidance. Any ideas?

2 REPLIES 2
Anonymous
Not applicable

Hi @DOLEARY85, thank you for the reply. Unfortunately, I'm looking for a solution within Power BI Report Builder and the measure solution you suggested doesn't apply.

DOLEARY85
Resident Rockstar
Resident Rockstar

HI,

 

I actually just wrote a post about doing something very similar, you should be able to use the same format measure - hopefully this will help:

 

https://community.powerbi.com/t5/Desktop/Multiple-Formats-in-a-Single-Column-with-Calculating-Totals...

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors