# DAX Measure Optimization for Multiple Tables

#### macfuller

##### Active Member
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:

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### ralliartur

##### New Member
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:

#### macfuller

##### Active Member
Thanks - a very intriguing way of structuring the logic!