DAX comparison operations do not support comparing values of type Number with values of type Text.

[Ndifor01]

Thank you all for the support. 

[Sahir_Maharaj · Accepted solution]

Hello @Ndifor01,

Can you please try this approach:

context = 
SELECTCOLUMNS(
    'Combined',
    "time_start", IF(ISBLANK('Combined'[_source.startedAt]) || FIND("@", 'Combined'[_source.startedAt], 1, 0) = 0, BLANK(), TIMEVALUE(LEFT(TRIM(MID('Combined'[_source.startedAt], FIND("@", 'Combined'[_source.startedAt]) + 2, LEN('Combined'[_source.startedAt]))), 8))),
    "time_end", IF(ISBLANK('Combined'[_source.endedAt]) || FIND("@", 'Combined'[_source.endedAt], 1, 0) = 0, BLANK(), TIMEVALUE(LEFT(TRIM(MID('Combined'[_source.endedAt], FIND("@", 'Combined'[_source.endedAt]) + 2, LEN('Combined'[_source.endedAt]))), 8))),
    "account_id", FORMAT('Combined'[_source.accountId], "0"),  -- Convert account ID to text
    "transact_status", SWITCH(
        TRUE(),
        CONTAINSSTRING('Combined'[_source.status], "FAILED_client"), "Client failed",
        CONTAINSSTRING('Combined'[_source.status], "SUCCESS_"), "Transaction succeeded",
        CONTAINSSTRING('Combined'[_source.status], "pending"), "Transaction Pending",
        CONTAINSSTRING('Combined'[_source.status], "gateway_"), "Pending at Gateway",
        CONTAINSSTRING('Combined'[_source.status], "FAILED_finish"), "Transaction failed",
        CONTAINSSTRING('Combined'[_source.status], "aborted_"), "Client aborted",
        CONTAINSSTRING('Combined'[_source.status], "wallet_"), "Pending from wallet",
        BLANK()
    ),
    "Date", VAR DatePart = LEFT('Combined'[_source.startedAt], SEARCH("@", 'Combined'[_source.startedAt]) - 2)
            RETURN IF(ISERROR(DATEVALUE(DatePart)), BLANK(), DATEVALUE(DatePart)),
    "subscription", 'Combined'[_source.cardTransactionMetaData.verifiedMerchant.isSubscription],
    "source_fee", VALUE('Combined'[_source.feeDetail.sourceFee.amount]),
    "free_limit_amount", IFERROR(VALUE(TRIM('Combined'[_source.feeDetail.destinationFee.freeLimitAmount])), 0),
    "VAT_total_revenue", IFERROR(VALUE(TRIM('Combined'[_source.cardMetaData.totalVATAmount])), 0),
    "govt_fees", IFERROR(VALUE(TRIM('Combined'[_source.feeDetail.sourceFee.governmentFee.fee])), 0),
    "product_fee", IFERROR(VALUE(TRIM('Combined'[_source.feeDetail.destinationFee.conversion.productFeeAmount])), 0),
    "feeVAT", IFERROR(VALUE(TRIM('Combined'[_source.feeDetail.sourceFee.feeVATAmount])), 0),
    "margin", IFERROR(VALUE(TRIM('Combined'[_source.feeDetail.sourceFee.margin])), 0),
    "total_amount", IFERROR(VALUE(TRIM('Combined'[_source.totalAmount])), 0)
    -- Continue adding fields with similar logic as required
)

Hope this helps.

[FarhanJeelani]

The error you're encountering indicates that there’s a mismatch between number and text data types in one or more columns or fields. This often happens when a column that should contain only numbers (integers, decimals) has text values (such as blank strings or non-numeric text), which can cause issues in DAX expressions and calculations.

To troubleshoot this, here are some steps and specific areas in your DAX code to check and adjust for consistent data types:

Step 1: Use `VALUE` to Convert Text to Numeric Values

The `VALUE` function is often used to ensure that text fields containing numeric values are correctly interpreted as numbers. In your code, some fields are already wrapped in `VALUE`, but make sure that all numeric fields are consistently wrapped this way if they could contain text representations of numbers.

Step 2: Identify Specific Areas for Potential Type Mismatches

From your code, here are fields that might have issues with text and number type mismatches. Consider adjusting them if necessary:

1. `source_fee`, `free_limit_amount`, VAT_total_revenue`, `govt_fees`, `product_fee`, `feeVAT`, `margin`, `total_amount`:
- These fields use `VALUE(TRIM(...))` or `VALUE(...)` in some places but not consistently. Ensure that any fields that may have blank or empty string values are converted using `VALUE`.

2. IF Statements with Numeric Defaults
- For fields like `running_bal`, check if there are default values (such as `0`) in the `IF` statements where they might be text instead of numeric. For example:
DAX

VALUE(IF(ISBLANK('Combined'[_source.feeDetail.sourceFee.feeVATAmount]) || 'Combined'[_source.feeDetail.sourceFee.feeVATAmount] = "", 0, 'Combined'[_source.feeDetail.sourceFee.feeVATAmount]))

3. Date Parsing in `Date` Variable
- Ensure that the `Date` variable returns a consistent date format. The `DATEVALUE` function should be able to handle the output from `LEFT` and `SEARCH` functions, but double-check that the parsed date is valid.

Step 3: Test Fields Individually

To narrow down where the error is occurring:
1. Test each field by creating simple calculated columns in Power BI using `VALUE` for each suspected column one at a time. This will help pinpoint which field might be causing the error.

2. If you find any column causing an issue, modify it by using `VALUE` consistently.

Step 4: Example Modification for the Code

Here’s an example of a modified section of your DAX code, with `VALUE` added to ensure numeric conversions:

DAX

"source_fee", VALUE('Combined'[_source.feeDetail.sourceFee.amount]),
"free_limit_amount", IFERROR(VALUE(IF(ISBLANK('Combined'[_source.feeDetail.destinationFee.freeLimitAmount]), "0", TRIM('Combined'[_source.feeDetail.destinationFee.freeLimitAmount]))), 0),
"VAT_total_revenue", IFERROR(VALUE(IF(ISBLANK('Combined'[_source.cardMetaData.totalVATAmount]), "0", TRIM('Combined'[_source.cardMetaData.totalVATAmount]))), 0),
"govt_fees", IFERROR(VALUE(IF(ISBLANK('Combined'[_source.feeDetail.sourceFee.governmentFee.fee]), "0", TRIM('Combined'[_source.feeDetail.sourceFee.governmentFee.fee]))), 0),
"product_fee", IFERROR(VALUE(IF(ISBLANK('Combined'[_source.feeDetail.destinationFee.conversion.productFeeAmount]), "0", TRIM('Combined'[_source.feeDetail.destinationFee.conversion.productFeeAmount]))), 0)

Final Note
After making these adjustments, re-evaluate the `context` table to ensure that all numeric fields are correctly parsed as numbers and all date fields as dates. If the error persists, try isolating specific parts of the code to identify any additional problematic areas. Let me know if you need further help!

[xifeng_L]

Since it was able to run normally in a small dataset before, but not afterwards, it is likely that the data type is incorrect. You can check whether the data type of each column is consistent with the previous one.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~