Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hopefully this isn't as complicated as it seems...
I am extracting data from Dynamics and in the extraction I have values that are coming into power bi in various columns instead of one based on the type of value (text, short text, integer, boolean, table, etc). How would I create a DAX formula that looks at the type of the value "First Type" and returns the value in the column that it corresponds to. So a value in "Text" in the First Type column would return the value from the "First AnswerText" column. The report is really messy with 7 value columns instead of one. Any help would be much appreciated.
Solved! Go to Solution.
I haven't had a chance to test it yet but it looks like it should work. Thank you!
You can unpivot part of the columns as below in Query Editor.
Then create relationship between two tables using the First Type column.
I create a Tag column to indentify the answer using following DAX formula.
Tag = IF ( Table1[First Type] = "ShortText", SEARCH ( "Text", Table1[Attribute],, -1 ), IF ( Table1[First Type] = "Table", SEARCH ( "First GroupAnswer2", Table1[Attribute],, -1 ), IF ( Table1[First Type] = "MultipleRadio" || Table1[First Type] = "MultipleCheckbox", SEARCH ( "First Checkbox", Table1[Attribute],, -1 ), SEARCH ( Table1[First Type], Table1[Attribute],, -1 ) ) ) )
At last, we only need to use the Tag column to get the desired answer.
Answer = CALCULATE ( MAX ( Table1[Value] ), Table1[Tag] > 0 )
Best Regards,
Herbert
Can you post some sample data and example of the desired output that you want? Having trouble figuring out what you are trying to do here. You may have to pivot/unpivot those columns.
I'm just looking to line up the multiple columns into one as shown below:
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
27 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |