Forum Discussion

sv98917n's avatar
sv98917n
Icon for Helper I rankHelper I
6 years ago
Solved

Duplicate Filter That Works With Date Slicer

Hello,

 

I currently have this column that shows how many times an id number appears:

 

Count Duplicates =
Var SegmentID = [SEGMENT_ID]
RETURN

CALCULATE(
COUNTROWS('table'),
all('Table'),
'Table'[SEGMENT_ID] = SegmentID
)
 
 

While this flag works for the whole report, it does not show values in accordance with my [created_date] date slicer. Yes, I do have a page level filter, but it does not change the DAX responces. So when I select a date range based on the created date, it says "3" or "2" to values that are duplictaes for the whole report range, but not the sliced range. 

 

Any help would be greatly appreciated.  Thank you!! 

 
Need Help
  • sv98917n's avatar
    sv98917n
    6 years ago

    He was able to work with this formula:

    (COUNTROWS (FILTER (allselected (table), table [SEGMENT_ID] at max (table [SEGMENT_ID])))
    Thank you for your help!

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    By any chance, does this work?

    Count Duplicates =
    Var SegmentID = [SEGMENT_ID]
    RETURN
    CALCULATE(COUNTROWS('FULCRUM_311_SRVREQ_NRT'),'Table'[SEGMENT_ID] = SegmentID)
    Also, you must create a Calendar Table and build a relationship from the Date column of the base Table.  To your visual, drag dates from the Calendar Table.
    If this does not help, then share some data in a format that can be pasted in an MS Excel workbook and clearly show the expected result there.
    • sv98917n's avatar
      sv98917n
      Icon for Helper I rankHelper I
      6 years ago
      Count Duplicates =
      Var SegmentID = [SEGMENT_ID]
      RETURN

       

      CALCULATE(
      COUNTROWS('Table'),
      all('Table'),
      'Table'[SEGMENT_ID] = SegmentID
      )
       
       
      this is my original formula. It was written wrong. Everything is coming from the same table. Thank you. 
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    sv98917n , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Not able to get with queries, what exactly it is doing

    • sv98917n's avatar
      sv98917n
      Icon for Helper I rankHelper I
      6 years ago

      He was able to work with this formula:

      (COUNTROWS (FILTER (allselected (table), table [SEGMENT_ID] at max (table [SEGMENT_ID])))
      Thank you for your help!
      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi sv98917n ,

         

        Glad that you have resolved it. Can you please accept your answer as solution? Others will benefit from it. Thanks!