Forum Discussion

slschmidt's avatar
slschmidt
Frequent Visitor
2 years ago
Solved

Switch-Performance

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. 
Need Help
  • slschmidt's avatar
    slschmidt
    2 years ago

    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!!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      slschmidt
      Frequent Visitor
      2 years ago

      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's avatar
      slschmidt
      Frequent Visitor
      2 years ago

      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!
    • slschmidt's avatar
      slschmidt
      Frequent Visitor
      2 years ago

      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?