Forum Discussion

gaiusgw's avatar
gaiusgw
Icon for Helper III rankHelper III
5 years ago
Solved

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.
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

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

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

7 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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])

     

     

    Regards

     

    Phil

    • gaiusgw's avatar
      gaiusgw
      Icon for Helper III rankHelper III
      5 years ago

      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. 

    • gaiusgw's avatar
      gaiusgw
      Icon for Helper III rankHelper III
      5 years ago

      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. 

       

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    5 years ago

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

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

    • gaiusgw's avatar
      gaiusgw
      Icon for Helper III rankHelper III
      5 years ago

      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.