Modification to Time intelligence measure

[Anonymous]

Hi all,

I'm hoping someone can help with a modification I need to make to some existing time time intelligence measures.

I originally had a large data table (10,000+ rows) of information relating to customer support documents, and how often each document has been used in a support scenario. Rows included the customer name for each document, the RefNum for the support ticket that resulted from each support scenario (RefNum did not contain unique numbers, because a customer support document might have been used in multiple different support scenarios. So each row in the table represented a single support scenario), the Full Name of the support analyst who handled the case, the timestamp 'DateActivityCreated' of when the case was generated, and the title of the support document. Example below:

Tenant	RefNum	Full Name	DateActivityCreated	Doc Title
Google	9193141	Jenny Jenson	2/12/2019 14:18	Google - Pin@store Error - Card could not be written or read
Alphabet	9193142	Jenny Jenson	14/01/2020 14:16	Alphabet - Application - Adobe products license issue
Apple	9193144	Jenny Jenson	2/12/2019 14:18	Apple - Password Reset - NPE
Google	9193145	Jenny Jenson	2/12/2019 14:19	Google - Postlink - Freezing in Parcel Value Field
IBM	9193147	Jimmy James	2/12/2019 14:17	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	2/12/2019 14:18	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	2/12/2019 14:18	IBM - Network (LAN) Account Unlock
IBM	9193147	Cameron Camson	2/12/2019 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jenny Jenson	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
IBM	9193147	Jimmy James	14/01/2020 14:16	IBM - Network (LAN) Account Unlock
ICC	9193151	Cameron Camson	2/12/2019 14:19	ICC - eShip - Child Accounts
Alphabet	9193152	Cameron Camson	2/12/2019 14:18	Alphabet - JDE - Unable to save the report because the network drive is missing
Samsung	9193154	Jackie Jackson	14/01/2020 14:16	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Samsung	9193154	Jackie Jackson	2/12/2019 14:18	Samsung - New user request
Google	9193168	Jackie Jackson	2/12/2019 14:22	Google - NZTA TXN Error - 953 timed out waiting for response from CDI
Blackberry Limited	9193172	Jimmy James	2/12/2019 14:21	Blackberry Limited – Email – Outlook Issues (Generic)
Blackberry Limited	9193172	Jimmy James	14/01/2020 14:16	Blackberry Limited – Email – Outlook Issues (Generic)
Blackberry Limited	9193172	Jimmy James	2/12/2019 14:21	Blackberry Limited – Email – Outlook Issues (Generic)
Samsung	9193174	Jimmy James	2/12/2019 14:21	Samsung - Disable user request
ICC	9193175	Jimmy James	2/12/2019 14:22	ICC - eShip - Printer Issues (newly onboarded user)
Alphabet	9193177	Jenny Jenson	2/12/2019 14:21	Alphabet - Forcepoint - Email Releases
Alphabet	9193177	Jenny Jenson	14/01/2020 14:16	Alphabet - Forcepoint - Email Releases

I wanted to be able to create a summary table, that showed a unique list of the support documents, the customer name for each document, and some measures. Here's an example (the columns in bold are the ones I needed):

Customer	DocTitle	Count Links (Last Month)	Count Links (Last Month -2)	% Change From (Last Month -2)	Rank (Last Month)	Rank (Last Month -2)	Rank Change (From Last Month-2 to Last Month)
IBM	IBM - Network (LAN) Account Unlock	5	4	25%	1	2	-1
Alphabet	Alphabet - Application - Adobe products license issue	1	0	New	2	N/A	2
Samsung	Samsung - New user request	1	5	-80%	2	1	-1
Blackberry Limited	Blackberry Limited – Email – Outlook Issues (Generic)	1	2	-50%	2	3	-1
Alphabet	Alphabet - Forcepoint - Email Releases	1	1	0%	2	4	-2
Google	Google - Pin@store Error - Card could not be written or read	0	1	-100%	N/A	4	-4
Apple	Apple - Password Reset - NPE	0	1	-100%	N/A	4	-4
Google	Google - Postlink - Freezing in Parcel Value Field	0	1	-100%	N/A	4	-4
ICC	ICC - eShip - Child Accounts	0	1	-100%	N/A	4	-4
Alphabet	Alphabet - JDE - Unable to save the report because the network drive is missing	0	1	-100%	N/A	4	-4
Google	Google - NZTA TXN Error - 953 timed out waiting for response from CDI	0	1	-100%	N/A	4	-4
Samsung	Samsung - Disable user request	0	1	-100%	N/A	4	-4
ICC	ICC - eShip - Printer Issues (newly onboarded user)	0	1	-100%	N/A	4	-4

And here's how the fields would intended to be calculated:

1) Count Links (Last Month) = count of how many times each support document has been used (based on how many rows it appears in, in the month preceeding whatever month it is currently)
2) Count Links (Last Month -2) = count of how many times each support document has been used (based on how many rows it appears in, in the month BEFORE the month preceeding whatever month it is currently)
3) % Change From (Last Month -2) = This would be calculated as (#3-#2)/#2, except where #2 = 0, in which case the result should be "New"
4) Rank (Last Month) = for the top 20 support documents only, this will show a ranking based on #1
5) Rank (Last Month -2) = for the top 20 support documents only, this will show a ranking based on #1 (in the month BEFORE the month preceeding whatever month it is currently)
6) Rank Change (From Last Month-2 to Last Month) = the difference between #4 and #5

A very kind person put together the below for me, which worked a treat (they used "Date(2020,1,1)" as the current date for testing, which I replaced Now() 😞

Summary = 
SUMMARIZE(
          'Table',
          'Table'[Tenant],
          'Table'[Doc Title],
          "Count Links (Last Month)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-1),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-2)+1
                        )
                   )
          ),
          "Count Links (Last Month -2)",
          CALCULATE(
                   COUNTROWS('Table'),
                   FILTER(
                        'Table',
                        AND(
                            'Table'[DateActivityCreated]<=EOMONTH(DATE(2020,2,1),-2),
                            'Table'[DateActivityCreated]>=EOMONTH(DATE(2020,2,1),-3)+1
                        )
                   )
          )

)

And here are the measures:

% Change From (Last Month -2) = 
var _lastmonth2 = SUM(Summary[Count Links (Last Month -2)])
var _lastmonth = SUM(Summary[Count Links (Last Month)])
return
IF(
    _lastmonth2 <> 0,
    SUMX(
         Summary,
         (_lastmonth-Summary[Count Links (Last Month -2)])/Summary[Count Links (Last Month -2)]
    ),
    "New"
)

Rank (Last Month) = 
RANKX(
    ALLSELECTED(Summary),
    CALCULATE(SUM(Summary[Count Links (Last Month)])),
    ,
    DESC,
    Dense
)

Rank (Last Month -2) = 
RANKX(
      ALLSELECTED(Summary),
      CALCULATE(SUM(Summary[Count Links (Last Month -2)])),
      ,
      DESC,
      Dense
)

Rank Change (From Last Month-2 to Last Month) = [Rank (Last Month)]-[Rank (Last Month -2)]

The kind person then advised me to go to the table level filter and configure both the values of Rank(Last Month) and Rank(Last Month - 2) with 'is less than or equal to 20':

This all works well, but I'm finding with my live data set that the 'Rank (Last Month)' often returns a duplicate value, because sometimes the 'Count Links (Last Month)' or 'Count Links (Last Month -2)' calculated columns are returning duplicate values in those instances:

What I'd like is to be able to do is have a completely (as much as I can) unique 'Rank (Last Month)' field, by adding the followig logic (in bold):

Rank (Last Month) = for the top 20 support documents only, this will show a ranking based on the following rules:

1) Count of how many times each support document has been used (based on how many rows it appears in, in the month preceeding whatever month it is currently)
2) Then, count of how many times each support document has been used (based on how many rows it appears in, in the month preceeding whatever month it is currently, -2) (i.e. the previous month)
3) Then, '% Change (From Month Prior)
4) Then, base the ranking on which Doc Title appears first in the alphabetised list.

I'd really appreciate any assistance here!

[kentyler]

this problem is generic when you use ranking... there seems to also be a generic solution

1) take the number you want to rank

2) multiply it by 1000

3) add the result of calling  RANDBETWEEN(0,999)
the extra three digits should mean that you will no longer have duplicate numbers in the column you are trying to rank

so your code

RANKX(
      ALLSELECTED(Summary),
      CALCULATE(SUM(Summary[Count Links (Last Month -2)])),

might change the expression inside the SUM

to be

(Summary[Count Links (Last month -1)] * 1000) + RANDBETWEEN(0,999)

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.

[Anonymous]

Thanks for the respons @kentyler - I'm getting the following error, am I doing something obviously wrong?

[kentyler]

You might have to do the multiplication in a calculated column in the table.

[Anonymous]

Thanks, but I'm not sure this is what I need. I've only partially played around with your suggestion, but it seems to have the effect of randomizing the results to a degree, rather than creating a unique ranking based on a series of rules: