CODEWITHSUNDEEP
powerbiswitch-statementdax
Tyler Brown
Tyler Brown

Reputation: 3

LEFT function using SWITCH: "DAX Comparison operations do not support comparing values of type Integer with values of type Text."

This is my first time posting to stackoverflow and have just broke into the PowerBI world yesterday since my company uses Access and VB code to write all reports and I have had enough of that.

I'm currently trying to create a new column that will write the series number. This can be achieved by using the first 2 or 4 digits of the PNUM. For example, series 20 would be denoted by either 2003, 2010 or RH201564, CH203456,etc. For my new column, I want all of those results to say 20.

Below is my current code for this column, using the LEFT function within the SWITCH. I do not understand when I'm getting this error mentioned in the title.

FormulaCode2 = 
SWITCH(
        LEFT('Overall Product Portfolio Planning'[PNUM],2 ="RH"),20,
        LEFT('Overall Product Portfolio Planning'[PNUM],2 ="ZR"),20,
        LEFT('Overall Product Portfolio Planning'[PNUM],2 ="CD"),20
        )

NOTE: At first I attempted the easiest way by trying to create a VAR of a list such as {"A".."z"} to capture all possible forms of a AA-zz that could appear in front of the series. However, I could not get very far. I wish DAX made use of wildcards, it would so much easier to put ??20 and just be done with it!

I'll appreciate any help with anyone that reads my long explanation. I hope to contribute back to this community and learn as much as I can! Thanks.

The [PNUM] Column

Upvotes: 0

Views: 1163

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Your syntax is a bit off. The first argument of SWITCH is what to check against.

I think this is what you're trying to write.

FormulaCode2 =
SWITCH (
    LEFT ( 'Overall Product Portfolio Planning'[PNUM], 2 ),
    "RH", 20,
    "ZR", 20,
    "CD", 20
)

On the other hand, if you want to extract the 3rd and 4th characters, then you can use MID instead of LEFT.

MID ( 'Overall Product Portfolio Planning'[PNUM], 3, 2 )

Upvotes: 1

Related Questions