Switch-Performance

[slschmidt]

I am trying to assign a segment code to my customters based on a calculation (in this case average 12 month net adt) over the past 12 months.  I created the following measures.  It works in the desktop app but when I publish it to the service, it does not have enough memory for the visual.  THe switch statement in the desktop app is even a little slow.  Ultimately, we need to be able to publish this to the service though.  

Net Theo = [Slot Theo Win Carded]+[Table Theo Win Carded]-[Slot XC Used Carded]

Slot Visits =

calculate(DISTINCTCOUNT(DailyDetailReport[AccountingDate]), DailyDetailReport[total games played]>0)

Segment L12M Net ADT =

var enddate = max('Calendar'[Date])

var nettheo = CALCULATE([Net Theo], DATESINPERIOD('Calendar'[Date], enddate, -12, month))

var slotsessions=CALCULATE([Slot Visits], DATESINPERIOD('Calendar'[Date], enddate, -12, month))

var l12mnetadt=CALCULATE(ROUND(DIVIDE(nettheo, slotsessions), 2))

var segment1=SWITCH(TRUE(), l12mnetadt>=1000, 1, l12mnetadt>=750, 2, l12mnetadt>=500, 3, l12mnetadt>=400, 4, l12mnetadt>=300, 5, l12mnetadt>=200, 6, l12mnetadt>=150, 7, l12mnetadt>=100, 8, l12mnetadt>=75, 9, l12mnetadt>=50, 10, l12mnetadt>=25, 11, l12mnetadt>=10, 12, l12mnetadt>=5, 13, 14)

var dailtable=CALCULATETABLE(DailyDetailReport, DATESINPERIOD('Calendar'[Date], enddate, -12, month))

return

SUMX(SUMMARIZE(dailtable, PlayerInfo[PlayerID],  "nettheo", nettheo, "slotsessions", slotsessions,"l12mnetadt", l12mnetadt, "Segment", segment1), segment1)

I created a table visual with the PlayerID and Segment along with other information like their address, phone number, and other calculated revenue information.  

The dailydetailreport table includes the daily revenue information by PlayerID and accounting date.    

Any thoughts on a better way is greatly appreciated. 

[Anonymous]

Hi @slschmidt ,

Please try to update the formula of measure [Segment L12M Net ADT] as below and check if the related visual can display the data normally in Service. 

Segment L12M Net ADT =
VAR enddate =
    MAX ( 'Calendar'[Date] )
VAR dailtable =
    CALCULATETABLE (
        DailyDetailReport,
        DATESINPERIOD ( 'Calendar'[Date], enddate, -12, MONTH )
    )
VAR summary =
    SUMMARIZE ( dailtable, PlayerInfo[PlayerID] )
VAR results =
    ADDCOLUMNS (
        summary,
        "nettheo", CALCULATE ( [Net Theo] ),
        "slotsessions", CALCULATE ( [Slot Visits] ),
        "l12mnetadt", [nettheo] / [slotsessions],
        "Segment",
            SWITCH (
                TRUE (),
                [l12mnetadt] >= 1000, 1,
                [l12mnetadt] >= 750, 2,
                [l12mnetadt] >= 500, 3,
                [l12mnetadt] >= 400, 4,
                [l12mnetadt] >= 300, 5,
                [l12mnetadt] >= 200, 6,
                [l12mnetadt] >= 150, 7,
                [l12mnetadt] >= 100, 8,
                [l12mnetadt] >= 75, 9,
                [l12mnetadt] >= 50, 10,
                [l12mnetadt] >= 25, 11,
                [l12mnetadt] >= 10, 12,
                [l12mnetadt] >= 5, 13,
                14
            )
    )
RETURN
    SUMX ( results, [Segment] )

In addition, you can refer the following links to optimize your DAX...

DAX Best Practice Guide

Optimizing DAX Video Course

Use Performance Analyzer to examine report element performance (can see and record logs that measure how each of your report elements performs when users interact with them, and which aspects of their performance are most (or least) resource intensive.)

Best Regards

[slschmidt]

I am sorry.  I thought I had it.  I used this:

Segment L12M Net ADT 3 =

VAR enddate =

    MAX ( 'Calendar'[Date] )

VAR dailtable =

    CALCULATETABLE (

        DailyDetailReport,

        DATESINPERIOD ( 'Calendar'[Date], enddate, -12, MONTH )

    )

VAR nettheo = CALCULATE([Net Theo])

VAR slotsessions=CALCULATE([Slot Visits])

VAR l12mnetadt=CALCULATE(nettheo/slotsessions)

VAR summary =

    SUMMARIZE ( dailtable, PlayerInfo[PlayerID] )

VAR results =

    ADDCOLUMNS (

        summary,

        "nettheo", CALCULATE (nettheo),

        "slotsessions", CALCULATE (slotsessions ),

        "l12mnetadt", CALCULATE(l12mnetadt),

        "Segment",

            SWITCH (

                TRUE (),

                l12mnetadt >= 1000, 1,

                l12mnetadt >= 750, 2,

                l12mnetadt >= 500, 3,

                l12mnetadt >= 400, 4,

                l12mnetadt >= 300, 5,

                l12mnetadt >= 200, 6,

                l12mnetadt >= 150, 7,

                l12mnetadt >= 100, 8,

                l12mnetadt >= 75, 9,

                l12mnetadt>= 50, 10,

                l12mnetadt >= 25, 11,

                l12mnetadt >= 10, 12,

                l12mnetadt >= 5, 13,

                14

            )

    )

RETURN

    SUMX ( results, [Segment] )

The issue now is that the nettheo, slotsessions, and l12mnetadt are not calculating on the last 12 months.  

When I change my variables to 

VAR nettheo = CALCULATE([Net Theo], DATESINPERIOD, calendar[Date], enddate, -12, month)

It bogs it down.  Ugh.  I am not sure what to do.  I marked my solution but in fact it is not the solution.  Can you further help me please?

[slschmidt · Accepted solution]

Thank you again for your help!  Here is what I changed and it worked:

Segment L12M Net ADT =

VAR enddate =

    MAX ( 'Calendar'[Date] )

VAR dailtable =

    CALCULATETABLE (

        DailyDetailReport,

        DATESINPERIOD ( 'Calendar'[Date], enddate, -12, MONTH )

    )

VAR nettheo = CALCULATE([Net Theo])

VAR slotsessions=CALCULATE([Slot Visits])

VAR l12mnetadt=CALCULATE(nettheo/slotsessions)

VAR summary =

    SUMMARIZE ( dailtable, PlayerInfo[PlayerID] )

VAR results =

    ADDCOLUMNS (

        summary,

        "nettheo", CALCULATE (nettheo),

        "slotsessions", CALCULATE (slotsessions ),

        "l12mnetadt", CALCULATE(l12mnetadt),

        "Segment",

            SWITCH (

                TRUE (),

                l12mnetadt >= 1000, 1,

                l12mnetadt >= 750, 2,

                l12mnetadt >= 500, 3,

                l12mnetadt >= 400, 4,

                l12mnetadt >= 300, 5,

                l12mnetadt >= 200, 6,

                l12mnetadt >= 150, 7,

                l12mnetadt >= 100, 8,

                l12mnetadt >= 75, 9,

                l12mnetadt>= 50, 10,

                l12mnetadt >= 25, 11,

                l12mnetadt >= 10, 12,

                l12mnetadt >= 5, 13,

                14

            )

    )

RETURN

    SUMX ( results, [Segment] )

I cannot thank you enough!!

[slschmidt]

Thank you so much for responding. 

I am getting errors at 

"l12mnetadt", [nettheo] / [slotsessions] and in the switch statement.

Cannot find name nettheo.

Cannot find name slotsessions

Cannot find name l12mnetadt

I appreciate your willingness to assist me!