Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Count unique reference numbers based on date slicer

 

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

 

URNDateCount
Customer 101/01/20192
Customer 101/08/20192
Customer 201/01/20194
Customer 201/08/20194
Customer 201/01/20204
Customer 201/01/20184

 

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

 

URNDateCount
Customer 101/01/20192
Customer 101/08/20192
Customer 201/01/20170
Customer 201/08/20191
Customer 201/01/20200
Customer 201/01/20180

 

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?

1 ACCEPTED SOLUTION
JarroVGIT
Resident Rockstar
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):

image.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
JarroVGIT
Resident Rockstar
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):

image.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors