Similar to "CASE" in SQL in multiple columns

[Anonymous]

Hi,

i'm new in Power BI. I got this script from a Spotfire report with a calculated column using SQL. Now, i'm trying to figure out how to do the same in Power BI.

I have a table with more than 4mi rows. In Spotfire, the script is like:

CASE WHEN [COLUMN1] in

("CY100", "CY101", "CY102", "CY103", "CY104", "CY105", "CY106", "CY107", "CY108", "CY109", "CY110", "CY111", "CY112", "CY113", "CY114", "CY115", "CY116", "CY117", "CY118", "CY119", "CY120", "CY121", "CY122")

THEN "AREA X"

WHEN [COLUMN1] in 

"CP001", "CP002", "CP003", "CP004", "CP005", "CP006", "CP007", "CP008", "CP009", "CP010", "CP011", "CP012", "CP013", "CP014", "CP015", "CP016", "CP204", "CP017", "CP018", "CP019", "CP020", "CP021", "CP022", "CP023", "CP024", "CP025", "CP026", "CP027")

THEN "AREA Y"

WHEN [Eq] in

("CG", "CS", "CI", "CN", "PF", "PM", "PV", "PD")

THEN "AREA Z"

WHEN  [Eq]

in ("CJ", "CK", "CH", "CD", "CE")

Then "AREA A" 

WHEN [WORKCENTER] in

("CLESCRJS", "CNORCRJS", "CENGEMED")

THEN "AREA B" 

"EQ" is a SUBSTRING from [COLUMN1]  = SUBSTRING ( [COLUMN1], 1, 2 )

WORKCENTER is a simple column

Thanks!

[mahoneypat]

This would be a good application of Groups columns.  See the link below.  When a column has many values, you can group them together into a smaller number subgroups, and a new column is added to your table with those fewer values.  In your case, you could group that list of values into a single category, and then use that single value in your measures, axes, legends, etc. instead of using an IN expression.

https://www.tutorialgateway.org/create-groups-in-power-bi/

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks for the answers, but the COLUMN1 contains more than 600 values... To create a group, i need to select all the values, right?

I tried to create a new table, use left function and make a relationship, but there are similar values, ex:

WHEN [COLUMN1] in 

"CP001", "CP002", "CP003", "CP004", "CP005", "CP006", "CP007", "CP008", "CP009", "CP010", "CP011", "CP012", "CP013", "CP014", "CP015", "CP016", "CP204", "CP017", "CP018", "CP019", "CP020", "CP021", "CP022", "CP023", "CP024", "CP025", "CP026", "CP027", "CG029", "CG030", "CG031", "CG032", "CG033", "CG034", "CG035",)

THEN "AREA Y"

When [Eq] in ("CG", "CS", "CI", "CN", "PF", "PM", "PV", "PD", "PG", "EM", "EV", "ES", "PS", "PK", "EC", "EN", "PN", "EG", "PC", "ED", "PJ", "EJ", "EL", "EB", "CW", "PB", "EA", "EO", "EI", "EU", "EK", "PI", "SN", "ER") Then "Proprio"

[v-yingjl]

Hi @Anonymous ,

If you want a new table with a column in power bi which is like using substring in SQL, you can use left() like this to extract them:

Table 2 =
SELECTCOLUMNS ( 'Table', "String", LEFT ( 'Table'[Column1], 2 ) )

Then, using switch() to add another column based on your filters like this:

column = 
SWITCH(
    [String],
    "CP","Proprio",
    "CG","Proprio",
    "AA","AAA"
)

Best Regards,
Yingjie Li

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

[Anonymous]

Hi,

@v-yingjl, thanks a lot

Actually, i need to use a left function as a "step 2"

First, i need to find the exactly value. If the result is not true, i need to use a left function from the same column. 

If the result still false, then, i need to go to another column, "workcenter".

in SQL, the script is like this (basically... it has more than 600 values)