DAX Measure Optimization for Multiple Tables

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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