Forum Discussion

Bansi008's avatar
Bansi008
Icon for Helper III rankHelper III
2 years ago

need help writing syntax for custom column

Hi All-- I have below sample data where I need to create a new custom column based on multiple conditional statements.

Conditions :-

1 - If Sector equals to Non-G and A vs B equals to A and No. of years data between 0-10 years then new column should populate value as "0-10 years"

2. If Sector equals Non-G, A vs. B equals A, and the number of years data is greater than 10, then the new column should populate the value "Over 10 years."

2. If Sector equals Non-G, A vs. B equals B,  then the new column should populate the value "All Years."

3. If sector equalls to G and No. years between 0 to 5 years then new column should populate value as "0-5 years"

4. If sector equalls to G and No. years between 5 to 15 years then new column should populate value as "5-15 years"

4. If sector equalls to G and No. years greater than 15 years then new column should populate value as "Over 15 years"  

 

No. of yearsA vs BSector
10AG
5BG
3BNon-G
5AG
2BNon-G
8BG
1ANon-G
0BG
6BNon-G
20AG
15BNon-G
13BG
Need Help

1 Reply

  • Igna's avatar
    Igna
    Icon for Resolver III rankResolver III
    2 years ago

    Hi,

     

    You can try

     

    New Column = 
IF(
    'Table'[Sector] = "Non-G" &&
    'Table'[A vs B] = "A" &&
    'Table'[No. of years] >= 0 && 'Table'[No. of years] <= 10,
    "0-10 years",
    IF(
        'Table'[Sector] = "Non-G" &&
        'Table'[A vs B] = "A" &&
        'Table'[No. of years] > 10,
        "Over 10 years",
        IF(
            'Table'[Sector] = "Non-G" &&
            'Table'[A vs B] = "B",
            "All Years",
            IF(
                'Table'[Sector] = "G" &&
                'Table'[No. of years] >= 0 && 'Table'[No. of years] <= 5,
                "0-5 years",
                IF(
                    'Table'[Sector] = "G" &&
                    'Table'[No. of years] > 5 && 'Table'[No. of years] <= 15,
                    "5-15 years",
                    IF(
                        'Table'[Sector] = "G" &&
                        'Table'[No. of years] > 15,
                        "Over 15 years",
                        BLANK()
                    )
                )
            )
        )
    )
)

     

    Hope it helps

     

    Igna