DAX - if/then giving error about integer values, when there are none?

[grggmrtn]

I'm working in three tables: The fact table, and two dimensions.

In the fact table I need to make a calculated column based on values (text) from columns in the two dimensions. Shouldn't be a problem, both dimensions are related to the fact table through key columns.

My DAX ended up a little bit complicated, but I didn't think it would be THIS complicated. Here's the code:

Frekvens Filter = 
IF (
    AND (
        OR (
            CONTAINS (
                RELATEDTABLE ( 'nexus2 Dim_Indsats' );
                'nexus2 Dim_Indsats'[Indsats navn]; "R1 - Rengøring*"
            );
            CONTAINS (
                RELATEDTABLE ( 'nexus2 Dim_Indsats' );
                'nexus2 Dim_Indsats'[Indsats navn]; "VP - Tøjvask*"
            )
        );
        CONTAINS (
            RELATEDTABLE ( 'nexus2 Dim_Frekvens' );
            'nexus2 Dim_Frekvens'[Hver x]; "WEEK"
        )
    );
    "OK";
    "NOT OK"
)

Pseudocode: if the value in  [Indsats navn] is either "R1 - Rengøring*" or "VP - Tøjvask*", and [Hver x] is "WEEK", then the value in the column should be "OK",  otherwise "NOT OK".

The problem is, this is giving me an error (sorry for having to translate the error message) "The function "CONTAINS" does not support comparing integer values with text values. Try using the function "VALUE" or "FORMAT" to convert one of the values.

Sorry, but the data type of all the involved columns is text, so where and why is PBI converting them? AKA, where did I screw up the dax? 😆

PS - yes, I KNOW this is a whole lot easier to do in M, but this is a HUGE report that was not kind on the server, so I'm trying to keep as much out of M as I possibly can. Plus, good learning experience, since my M is good but my DAX blows.

[V-lianl-msft]

Hi @grggmrtn ,

Please check and modify the data type of these fields involved.

You can also use FORMAT function with these fields.

If the problem persists,could you please share sample pbix with dummy data?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[grggmrtn]

Hi @V-lianl-msft 

The data type of all fields has been checked.

Dummy data shouldn't be necessary. There are three simple tables - 1 fact table with only key columns for each dimension, and two dimensions that have their respective key columns (values in them are integers, but I'm not using they keys in my code), and another column with the text values that I'm trying to compare.

Yes, I could use FORMAT, I'm just trying to figure out what integers Power BI thinks I'm using here?

[grggmrtn]

My bad - I overlooked something. @Mariusz gave a good answer 🙂

[Mariusz · Accepted solution]

Hello @grggmrtn

Try this.

Frekvens Filter = 
VAR __isWeek = RELATED( 'nexus2 Dim_Frekvens'[Hver x] ) = "WEEK"
VAR __isIn = RELATED( 'nexus2 Dim_Indsats'[Indsats navn] ) IN { "R1 - Rengøring*",  "VP - Tøjvask*" }
RETURN 
IF (
    __isWeek && __isIn;
    "OK";
    "NOT OK"
)

Best regards
Mariusz

If this post helps,then please consider accepting it as the solution.

Please feel free to connect with me.
Linkedin

[grggmrtn]

Hey @Mariusz - thanks for the quick reply!

Unfortunately, your code is giving me the same error as mine, and I still can't figure out why...