DAX Measure Optimization for Multiple Tables

[macfuller]

I have a brutally slow measure, and I need to create several more like it with contrasting logic in order to break down our spend in the categories a consultant is telling us to. Before I slow my model to a crawl I was hoping someone could help with optimization?

Controlled PO no Pharmacy :=
VAR ControlPORules =
    FILTER (
        Vouchers,
        Vouchers[Post Status] = "Posted"
            && Vouchers[Close Status] = "Open"
            && Vouchers[PO Compliance] = "Controlled PO"
            && LEFT (
                Vouchers[PwC Category],
                2
            ) <> "A)"
            && RELATED ( Orders[IsScripted] ) <> "Scripted"
            && RELATED ( Orders[Type] ) <> "Blanket"
            && RELATED ( Orders[Type] ) <> "Blanket Goods"
            && RELATED ( Orders[Type] ) <> "Blanket Svcs"
            && RELATED ( Orders[Type] ) <> "Svc Contract"
            && RELATED ( Dept[Node 7] ) <> "SUP PHARM"
    )
RETURN
    CALCULATE (
        [Direct Voucher Amount Total],
        ControlPORules
    )

You can see I am testing for conditions in 3 tables: Vouchers, Orders, and Dept. Orders and Dept are each 1:many related to the Vouchers table. Orders and Vouchers have several million rows. I've looked at this article at SQLBI but I might be failing to create the CALCULATETABLE measure in the right way.

Thanks for any help!

 

[ralliartur]

Basing on your measure I would propose:

Controlled PO no Pharmacy :=
SUMX (
    FILTER (
        CALCULATETABLE (
            Vouchers,
            Vouchers[Post Status] = "Posted",
            Vouchers[Close Status] = "Open",
            Vouchers[PO Compliance] = "Controlled PO"
        ),
        LEFT ( Vouchers[PwC Category], 2 ) <> "A"
    ),
    VAR _Types = "Blanket|Blanket Goods|Blanket Svcs|Svc Contract"
    VAR _IsScripted =
        RELATED ( Orders[IsScripted] ) <> "Scripted"
    VAR _TYPE =
        PATHCONTAINS ( _Types, RELATED ( Orders[Type] ) ) = FALSE ()
    VAR _Node7 =
        RELATED ( Dept[Node 7] ) <> "SUP PHARM"
    VAR _Calculate = _IsScripted * _TYPE
        * _Node7
    RETURN
        IF ( _Calculate = 1, [Direct Voucher Amount Total], BLANK () )
)

 

[macfuller]

Thanks - a very intriguing way of structuring the logic!