DAX calculated column hanging - optimize or build in Power Query?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
319
Office Version
  1. 365
Platform
  1. Windows
I have a calculated column to categorize spend types in a 8+ million row table. The existing version posted below works, but I need to add one more variable (potentially doubling the number of categories) and when I try it works in the data model window but Excel goes bye-bye when I go into full calculation mode. No error messages, just sits after 20 minutes with 0% in task manager and "Ready Calculate" in the status bar. I guess it's the Excel equivalent of too much tequila - there's a pulse but no motion.

I'm hoping there's a way to optimize my DAX below so that I can squeeze in more categories. Otherwise I can build this in Power Query, but merging two 8+ million row tables and not being able to use VAR in M is a daunting prospect. Any recommendations are welcome!

Code:
=VAR POCreateDate =
    RELATED ( Orders[PO Date] )
VAR Scripted =
    RELATED ( Orders[IsScripted] )
VAR IsBkt =
    IF (
        RELATED ( Orders[Type] ) = "Blanket"
            || RELATED ( Orders[Type] ) = "Blanket Goods"
            || RELATED ( Orders[Type] ) = "Blanket Svcs"
            || RELATED ( Orders[Type] ) = "Svc Contract",
        TRUE,
        FALSE
    )
VAR IsElectronicPO =
    IF (
        RELATED ( Orders[PO Origin Description] ) = "Cart PO"
            || RELATED ( Orders[PO Origin Description] ) = "P/S Clinical Service Inventory",
        TRUE,
        FALSE
    )
VAR AfterFactRule =
    IF (
        IsElectronicPO,
        Vouchers[Invoice Date] >= POCreateDate,
        Vouchers[Invoice Date] >= POCreateDate + 1
    )
RETURN
    IF (
        LEFT (
            Vouchers[PwC Category],
            2
        ) = "A)",
        "Excluded Spend",
        IF (
            Vouchers[PO Number] = "",
            "Non-PO",
            IF (
                ISBLANK ( POCreateDate ),
                "Unclassified PO - No History",
                IF (
                    IsBkt,
                    IF (
                        AfterFactRule,
                        IF (
                            Scripted = "Catalog",
                            "Controlled Blanket PO",
                            "Scripted Blanket PO"
                        ),
                        IF (
                            Scripted = "Catalog",
                            "After-the-Fact Blanket Catalog PO",
                            "After-the-Fact Blanket Scripted PO"
                        )
                    ),
                    IF (
                        AfterFactRule,
                        IF (
                            Scripted = "Catalog",
                            "Controlled Regular PO",
                            "Scripted Regular PO"
                        ),
                        IF (
                            Scripted = "Catalog",
                            "After-the-Fact Regular Catalog PO",
                            "After-the-Fact Regular Scripted PO"
                        )
                    )
                )
            )
        )
    )
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,215,069
Messages
6,122,951
Members
449,095
Latest member
nmaske

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top