Calculate repeat values from within user defined periods

[jonpoole]

I have a  table which looks like

ClientID	Date	Repeats
1	01/04/2018	3
2	02/04/2018	1
3	03/04/2018	1
4	04/04/2018	2
1	04/07/2017	3
6	05/07/2017	1
7	06/07/2017	2
7	07/07/2017	2
5	06/06/2017	1
1	07/06/2017	3
4	08/06/2017	2

Where repeats is a calcuated column:

RepeatCount = 
VAR ClientID = table[ClientID]
return CALCULATE(
    countrows(table),
    ALL(table),
    table[ClientID]= ClientId)

This works well to explain repeats across the whole time period and we have also been able to manage filters to constrain to a particular data range to (e.g. 2018 only) on demand, as required. 

However, user requirement has changed and there is a request to filter the repeats based on a user defined period. 

I'm aware that I can't use slicers to control the calculation, so we clearly need to rethink the calculation. 

Currently, I think if I could add an additional column to show the previous date for that Client then I could use a date reference table to control the slicer against those two colums...

So, powerBi gurus, a couple of questions:

- I'm lost on how to create that additional column - any ideas?

- I'm convinced this is not the most processor efficient/elegant way of achieving this - any broader thoughts on the problem?

thank you!! 

[v-yuta-msft]

Hi jonpoole,

You can create a measure using DAX as below:

RepeatCount =
CALCULATE (
    COUNTROWS ( Table1 ),
    ALLEXCEPT ( Table1, Table1[ClientID] ),
    ALLSELECTED ( Table1[Date] )
)

 

In addtion, based on my test, I'm afraid the priority of a slicer is very low so that we can't filter context based on a slicer but only use a slicer to filter other row context.

Regards,

Jimmy Tao

[bathnesresearch]

Thanks Jimmy, we got a very similar output, that's helpful.

The other requirement (neglected this in my OP) is for a summary graph of the instances (a count of duplicate counts) which is dynamic relative to the aforementioned date range (in essence, create a dynamic axis a using the measure filtered by the slicer).

My understanding from your post and some further search is that the slicer priority wouldn't allow this to happen, but I thought I'd double check.

thanks again!

[v-yuta-msft · Accepted solution]

Hi bathnesresearch,

"in essence, create a dynamic axis a using the measure filtered by the slicer."

<--- So your requirement is to create a dynamic calculate column based on which is your slicer, right? I'm afraid you can't achieve this because power bi doesn't support dynamic calculate column.

Regards,

Jimmy Tao

[tex628]

If you want to allow users to define the time range you can no longer use a calculated column for this. Use a measure, proberbly distinctcount() on the dates.

With a distinctcount measure you should be able to use a slicer to allow the user to view repeating dates within a timeframe.

/ J