Forum Discussion
Problems using SUMX, FILTER, and VALUE together
I ended up creating a calculated column that returns blank values for rows where there were letters in the codes. I end up with several "undefined" cases but still far better than doing everything manually, which is the current state.
Hi cathoms ,
Are you able to maybe do something at powerquery level to replace the alpha values with null. Your columns are still being defined as text with them containing the alpahnumeric, so the filter context doesn't resovle this. You could do something with an if statment column to replace the values and work your cacluation or a complex measure.
Sometimes the easiest is best. 🙂
Unfortunately, not in this case. We have database report writers who build most of our Power BI datasets and I am building a .pbix report based on such a dataset. To make any changes to the data model I need to use Direct Query mode, which doesn't give me the option to transform the data in Power Query.
I'm learning today that there are a number of very frustrating limits on what DAX functions can be used in creating new columns in DirectQuery mode.
Hi cathoms , Don't get me started on it. I've recently started developing reports in Dynamics and using DQ more frequently (have always imported data), so having to find different solutions and doing a lot more work upstream in the model, but these are the challenges I suppose. Let me have a think on it. I might come up with something for you.