Create calculated column using alphabetical range

[liberty20]

Hi all,

I am trying to create calculated column in Power BI report.

When,

If State column = " NSW" & First Char of Customer name column = (Present between)"A to F" Then assign name of executive as John,

If State column = " NSW" & First Char of Customer name column= (Present between)"G to K" Then assign name of executive as Kevin,
If State column = " QLD" & First Char of Customer name column= (Present between)"A to F" Then assign name of executive as Reith,

else "other"

Kindly help with the same.

Thank you in advance!!

[Anonymous · Accepted solution]

Hi @liberty20,

You can try to use the Unicode function to achieve your requirement:

formula =
VAR ATF =
    GENERATESERIES ( UNICODE ( "A" ), UNICODE ( "F" ), 1 )
VAR GTK =
    GENERATESERIES ( UNICODE ( "G" ), UNICODE ( "K" ), 1 )
RETURN
    SWITCH (
        Table[State],
        "NSW",
            IF (
                UNICODE ( LEFT ( Table[Customer name], 1 ) ) IN ATF,
                "John",
                IF ( UNICODE ( LEFT ( Table[Customer name], 1 ) ) IN GTK, "Kevin", "Other" )
            ),
        "QLD", IF ( UNICODE ( LEFT ( Table[Customer name], 1 ) ) IN ATF, "Reith", "Other" ),
        "Other"
    )

UNICODE function (DAX) - DAX | Microsoft Docs

Regards,

Xiaoxin Sheng