I had a requirement where my customer was looking to gain some insights into their data relating to reactivated players.

The definition used for a reactivated player is a player that has previously played but has not played for a certain number of days. The player then comes back and plays again. So, by this definition the player is reactivated.

What I had to do, to get this working so that it performed well was to put this into a calculated table. This ensured that I only had the data I required. Due to the sheer volume of data that I had to go through, trying to do this with a DAX measure proved in my example to be very slow. And I would rather create a calculated table which can perform much faster and give a greater reporting experience.

I explain below. It is quite Complex so please read carefully below with the colour coded sections

Overview: What this calculated table is doing, is counting how many days the player made an order between his last order and second to last order based on date.

NOTE: I am going to explaining this from the inside out.

Using either Power BI Desktop or Azure Analysis Services or SSAS Tabular 2017, I created a Calculated with the following syntax below.

  1. Reactivated Players Table =
  2. FILTER (
  3. CALCULATETABLE (
  4.    ADDCOLUMNS (
  5.     VALUES ( ‘Adventure Works'[Customer Name] ),
  6.      “Days Between Orders”
  7.       , CALCULATE (
  8.         DATEDIFF (
  9.          CALCULATE (
  10.           MAX ( ‘Adventure Works'[Order Date] ),
  11.           FILTER ( ‘Adventure Works’, ‘Adventure Works'[Order Date] <> MAX ( ‘Adventure Works'[Order Date] ) )
  12.          ),
  13.          MAX ( ‘Adventure Works'[Order Date] ),
  14.         DAY
  15.        )
  16.     ),
  17.   “Last Order Date”, CALCULATE ( MAX ( ‘Adventure Works'[Order Date]), ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) ),
  18.   “GeographyKey”, CALCULATE (
  19.         MAX ( ‘Adventure Works'[GeographyKey] ),
  20.         ALLEXCEPT ( ‘Adventure Works’, ‘Adventure Works'[Customer Name] ) )
  21.     )
  22.  ),
  23.  [Days Between Orders] <> BLANK ()
  24. )

          Lines 8 – 11
    • This DAX Calculation is getting the second to last date the player made an order.
      • This could be called “Second to Last Order Date”
    • Line 12
      • This DAX calculation is getting the last date the player made an order.
        • This could be called “Last Order Date”
      • Lines 7-8 & 13-15
        • This is the outer DAX CALCULATE which is using the DATEDIFF to count how many Days it was between the two dates above.
        • EG: DATEDIFF(“Second to Last Order Date”,” Last Order Date”,DAY)
      • Line 6
        • This is the name of the column that I am creating in my Calculated Table called “Days Between Orders”
        • Line 5
          • Here I am getting the VALUES for the column which is the Customer Name
          • NOTE: It is important here to always use the Customer Name or CustomerAccountKey in order to get back the values you are expecting
        • Line 17
          • I am creating another column called “Last Order Date” with another DAX measure
          • This column is getting the last time the player made an order. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Date table in my dimension model
          • NOTE: This is so that I can then use the Date table to filter multiple tables when accessing the reports.
        • Line 18 – 21
          • Once again I am creating another column called “GeographyKey” with a DAX Measure
          • I created this column, so that I can get the GeographyKey for each Customer Name. And to do that I had to use the ALLEXCEPT so that it would break the filter context of the entire table and do it for each Customer Name
          • The reason for this column is so that I can join this back to the Geography table in my dimension model
          • NOTE: This is so that I can then use the Geography table to filter multiple tables when accessing the reports.NOTE: If I wanted to create any more columns to link back to the Dimension tables they would be done in this section.
        • Lines 3-4 & 22-23
          • This is where I am specifying to ADDCOLUMNS to my CALCULATETABLE syntax. Which creates the in-memory table.
        • Lines 2,24-25
          • The final piece is what makes this table so fast. Is I am then filtering my entire table. This is so that I do not bring back records that I am not interested in.
          • I do this specifically in line 24, where I am using the column I created in lines 6 – 16 and filtering it out to say where the Days are NOT BLANK

Apologies for the formatting above, I just could not get it the way it was in Word!

Once the above is done, I then get back a subset of all the records, which then makes this table very fast to query.

Creating the Dynamic Days Between Reactivation table

To give the required flexibility to the client, I created a table using the What-If Parameters.

This then allowed the client to dynamically decide how many days there should be for a player to be deemed as Reactivating.

I then created a slicer for this table

Reactivated Players

This measure is created off the table created above. Which makes it run correctly. As well as being super-fast when being queried.

Reactivated Players = 
VAR SelectedValue =
    SELECTEDVALUE ( 'Days between Reactivation'[Days between Reactivation], 30 )
RETURN
    CALCULATE (
        COUNTROWS ( 'Reactivated Players Table' ),
        'Reactivated Players Table'[Days Between Orders] >= SelectedValue
    )
  • In this measure above, I am once again using the Days between Reactivation Slicer for the end user to determine how many days decide how long a player is re-activated from.
  • Then I am counting the rows where it is less than the Number of days between the bets.

Report in Action

Below is what the report looks like in action, where the user is using the Slider in order to determine how many players are reactivating.

NOTE: The reason that I put in the column called “Reactivated Players” is so that it shows that the measure is working as expected. So if the Slicer is set to 30 any player with “Days Between Orders” of less than 30, should not have a 1 next to them. And as the slider goes up, the players with the corresponding “Days Between Orders” should decrease or show less “1”

 

 

Conclusion

I have shown how to create the reactivated players DAX pattern. As well as allowing the user to dynamically select the period for reactivation.

As always, I welcome any comments or suggestions.

Here is a link to the PBIX file: FourMoo – Reactived Players.pbix