cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculate repeat values from within user defined periods

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculate repeat values from within user defined periods

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

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Calculate repeat values from within user defined periods

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


Connect on LinkedIn
Highlighted
Community Support
Community Support

Re: Calculate repeat values from within user defined periods

Hi jonpoole,

 

You can create a measure using DAX as below:

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

捕获.PNG 

 

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

Highlighted
Regular Visitor

Re: Calculate repeat values from within user defined periods

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!

Highlighted
Community Support
Community Support

Re: Calculate repeat values from within user defined periods

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

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors