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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors