Results 1 to 3 of 3

Thread: DAX Measure Optimization for Multiple Tables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    224
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default DAX Measure Optimization for Multiple Tables

    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 by macfuller; Aug 29th, 2018 at 10:46 AM.

  2. #2
    New Member
    Join Date
    Apr 2015
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX Measure Optimization for Multiple Tables

    Basing on your measure I would propose:

    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 by ralliartur; Aug 30th, 2018 at 04:58 AM.

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    224
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: DAX Measure Optimization for Multiple Tables

    Thanks - a very intriguing way of structuring the logic!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •