Forum Discussion

sahmed1048576's avatar
sahmed1048576
New Member
1 year ago
Solved

Formula errors

Hello, I have made this formula in excel:

=IF(OR(LEFT(X2,3)="101", LEFT(X2,3)="102", LEFT(X2,3)="103", LEFT(X2,3)="104", LEFT(X2,3)="105", LEFT(X2,3)="106"), "Chill Pallet",
IF(LEFT(X2,3)="418", "Ambient Shelving",
IF(AND(VALUE(LEFT(X2,3))>=401, VALUE(LEFT(X2,3))<=449), "Ambient Pallet",
IF(LEFT(X2,1)="2",IF(LEFT(X2,3)="205",
IF(AND(VALUE(MID(X2,4,3))>=70, VALUE(MID(X2,4,3))<=148, MOD(VALUE(MID(X2,4,3)),2)=0, ISNUMBER(MATCH(VALUE(MID(X2,7,2)),{10,20,30,40,50},0))), "CD Shelving", "CD Pallet"),
"CD Pallet"),"Other"))))

I want to now have it run in the Power BI and am having trouble converting this to DAX.

I am new to the Power BI platform, and was using Copilot/ Chat GPT but each would result in errors, any help would be appreciated!

General Comment
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,

    Thanks for lbendlin's concern about the problem and i want to offer some more infotmation for user to refer to.

    hello sahmed1048576 , you can try the following dax in a calculated column.

    Column =
IF (
    LEFT ( [X2], 2 ) IN { "101", "102", "103", "104", "105" },
    "Chill Pallet",
    IF (
        LEFT ( [X2], 3 ) = "418",
        "Ambient Shelving",
        IF (
            INT ( LEFT ( [X2], 3 ) ) >= 401
                && INT ( LEFT ( [X2], 3 ) ) <= 449,
            "Ambient Pallet",
            IF (
                LEFT ( [X2], 1 ) = "2",
                IF (
                    LEFT ( [X2], 3 ) = "205",
                    IF (
                        INT ( MID ( [X2], 4, 3 ) ) >= 70
                            && INT ( MID ( [X2], 4, 3 ) ) <= 148
                            && MOD ( INT ( MID ( [X2], 4, 3 ) ), 2 ) = 0
                            && VALUE ( MID ( X2, 7, 2 ) ) IN { 10, 20, 30, 40, 50 },
                        "CD Shelving",
                        "CD Pallet"
                    ),
                    "CD Pallet"
                ),
                "Other"
            )
        )
    )
)

    Best Regards!

    Yolo Zhu

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,

    Thanks for lbendlin's concern about the problem and i want to offer some more infotmation for user to refer to.

    hello sahmed1048576 , you can try the following dax in a calculated column.

    Column =
IF (
    LEFT ( [X2], 2 ) IN { "101", "102", "103", "104", "105" },
    "Chill Pallet",
    IF (
        LEFT ( [X2], 3 ) = "418",
        "Ambient Shelving",
        IF (
            INT ( LEFT ( [X2], 3 ) ) >= 401
                && INT ( LEFT ( [X2], 3 ) ) <= 449,
            "Ambient Pallet",
            IF (
                LEFT ( [X2], 1 ) = "2",
                IF (
                    LEFT ( [X2], 3 ) = "205",
                    IF (
                        INT ( MID ( [X2], 4, 3 ) ) >= 70
                            && INT ( MID ( [X2], 4, 3 ) ) <= 148
                            && MOD ( INT ( MID ( [X2], 4, 3 ) ), 2 ) = 0
                            && VALUE ( MID ( X2, 7, 2 ) ) IN { 10, 20, 30, 40, 50 },
                        "CD Shelving",
                        "CD Pallet"
                    ),
                    "CD Pallet"
                ),
                "Other"
            )
        )
    )
)

    Best Regards!

    Yolo Zhu

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