• Jobs
  • Bootcamp
  • About Us
  • For professionals
    • Home
    • Jobs
    • Courses and challenges
    • Questions
    • Teachers
    • Bootcamp
  • For business
    • Home
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

223
Views
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

12 months ago · Santiago Trujillo
1 answers
Answer question

0

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 )
12 months ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post job Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2023 PeakU Inc. All Rights Reserved.