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.
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:
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |