Forum Discussion
Count unique reference numbers based on date slicer
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?
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! 🙂
1 Reply
- JarroVGIT
Resident Rockstar
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! 🙂