Problems using SUMX, FILTER, and VALUE together

[cathoms]

Taking a different approach to a problem I posted about yesterday.

I'm trying to create a measure that I can use to flag types of coding changes based on the measure results. The codes are stored as strings. Unfortunately, a few codes within my existing columns are alphanumeric combinations, not just numbers.

I'm trying to filter those values out then subtract one column from the other but I get an error suggesting those items were not filtered. Can anyone help? DAX measure below followed by screenshot of the error.

CodeChangeDiff = 
SUMX (
        FILTER (
            UclFact,
            NOT ( [OriginalProcedureFinal] = "NCG11"
                || [OriginalProcedureFinal] = "G2212"
                || [OriginalProcedureFinal] = "N9211" )
        ),
        VALUE ( UclFact[OriginalProcedureFinal] )
    )
- 
SUMX (
            FILTER (
                UclFact,
                NOT ( [OriginalProcedureFinal] = "NCG11"
                    || [OriginalProcedureFinal] = "G2212"
                    || [OriginalProcedureFinal] = "N9211" )
            ),
            VALUE ( UclFact[CurrentProcedureFinal] )
        )

[davehus]

Hi  @cathoms , have your tried using a calculated column with an if statement to set the alphanumerics as 0. I tried it there on a DQ I have, it's not alphamumeric but it worked. In the above you are using an OR clause with the || should this not be AND() or &&?

[cathoms · Accepted solution]

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.

[davehus]

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. 🙂 

[cathoms]

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.

[davehus]

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.