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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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