Count unique reference numbers based on date slicer

[Anonymous]

I have a table with a count of URNs it it which currently looks like this

URN	Date	Count
Customer 1	01/01/2019	2
Customer 1	01/08/2019	2
Customer 2	01/01/2019	4
Customer 2	01/08/2019	4
Customer 2	01/01/2020	4
Customer 2	01/01/2018	4

I am using the Count as a slicer but I also have a date slicer and currently when filtering the dates, my count slicer stays the same.  What I am looking for something that will give a count but dynamic based on date range, for example

Date Filtered for whole of 2019

URN	Date	Count
Customer 1	01/01/2019	2
Customer 1	01/08/2019	2
Customer 2	01/01/2017	0
Customer 2	01/08/2019	1
Customer 2	01/01/2020	0
Customer 2	01/01/2018	0

I want to be able to use the Count as a slicer but linked to the date.  Not sure if this is possible either as a calculated column or somehow in visuals?

[JarroVGIT · Accepted solution]

Hi @Anonymous ,

First off, thanks for providing clear tables with expectations and what you currently have. That helps us recreate your situation and help you figure out a solution 🙂

Then to your problem. The "issue" with calculated columns is that they are not re-evaluated upon slicing or filtering. They are evaluated upon refresh of the dataset and never thereafter. Measures are (by nature) re-evaluated by every change in filter context. However, Measures also (by nature) return a single value and therefor can not be used in a slicer. Fortunately for us, they are allowed to be used in a Visual Filter. 

I created a measure like this: 

CountMeasure = 
VAR _curCustomer = SELECTEDVALUE('Table'[URN])
RETURN
CALCULATE(COUNTROWS('Table'), FILTER(ALLSELECTED('Table'), [URN] = _curCustomer)) 

Then I created a Table visual with column URN, Date (as Date) and Count (column). I then dragged the above measure to the Filter pane and I can dynamically filter the table visual by using the FIlter pane and the date slicer. For example, I want to only show customers that have a count of less then 3 for a certain date (in this case, for whole 2019). I expect 2 rows for customer 1 and 2 rows for customer 2 (it has 2 total rows for that year so my Filter pane will let it show us):

You can find my PBIX here, please ignore any other possible tables, they are for other questions.

Let me know if this helps you! 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂