Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
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
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!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |