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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gaiusgw
Helper III
Helper III

Return unique value list from date range slicer

I have used the method descibed in this link to do date range comparisons for sales but now I need to return a list based on the code. 

 

https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613

 

Here is what my measure looks like currently: 

 

Current Sales = (
VAR _Cuur_start = Min('Date'[Date])
VAR _Curr_END = Max('Date'[Date])
return
calculate(sum(OPENDET[BOOKED $]), OPENDET [ORDER DATE] >= _Cuur_start && OPENDET [ORDER DATE] <= _Curr_END ))
 
I need to edit this so that the return is a list of unique IDs between the date ranges. 'OPENDET'[ORDERNUMBER]
 
I would also like to make a version of this that does a count of unique IDs between the date ranges. 'OPENDET'[CUSTNUMBER]
 
Any help would be appeciated. Thanks.
1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Try to create a measure and apply it to visual level filter.

measure = if(max(OPENDET [ORDER DATE]) in values('Date'[Date]),1,0)

View solution in original post

7 REPLIES 7
V-lianl-msft
Community Support
Community Support

Try to create a measure and apply it to visual level filter.

measure = if(max(OPENDET [ORDER DATE]) in values('Date'[Date]),1,0)

Sorry i do not see how this plugs into my original formula. I have tried a few differetn ways but am only getting the max value which is the most recent date. What i need is to have the results of the table narrowed by the date slicer from the original formula because many other elements of my report are linked to those date slicers. 

PhilipTreacy
Super User
Super User

Hi @gaiusgw 

Can you please give an example of the result you want.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hopefully this screenshot clear things up. I am trying to make it so that results in the invoice box are narrowed down by date range. However, the date range method I am using makes it so that dates cannot have a relationship between databases. So i am looking to plug into the code in my original post. 

 

Capture.JPG

@PhilipTreacy anymore advise on this? Thanks a lot. 

PhilipTreacy
Super User
Super User

Hi @gaiusgw 

 

Download sample PBIX file

 

The List of Unique ID's can be created with this

 

List Unique IDs = 

CALCULATE(CONCATENATEX(DISTINCT(OpenDet[CustNumber]), ('OpenDet'[CustNumber]),", "))

 

 

and the count with this

 

Count Unique IDs = DISTINCTCOUNT(OpenDet[CustNumber])

 

 

unique.png

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hey Phil,

Thanks for examples and the file! However, I may has misspoke when I said "list" of unique returns. What I meant was narrowing down a table of IDs based on the date formula I am curretly using. 

 

The count part is what I needed for count though so that great. Thanks a lot. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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