Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Question | QuestionID | AnswerType |
How much money? | Q01 | Currency |
How's it Going? | Q02 | Multi-line Text |
"Answers" Table
Title | Answer | AnswersQuestionID |
Survey1 | $504 | Q01 |
Survey1 | Great! | Q02 |
Survey2 | $22 | Q01 |
Survey2 | Pretty Well | Q02 |
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:
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?
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.
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |