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!
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"
)
)
)
)
)
)