DAX Measure Optimization for Multiple Tables

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
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?

Code:
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!
 
Last edited:

ralliartur

New Member
Joined
Apr 15, 2015
Messages
30
Basing on your measure I would propose:

Rich (BB code):
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 () )
)

 
Last edited:

Forum statistics

Threads
1,086,257
Messages
5,388,710
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top