Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help needed with DAX

I am new to DAX functions and would appreciate your help. My formula works partially. I want to count the number of rows that are repeated but I want 0 where there is no number.

 

 

Formula used : 

# of Extensions = IF(ISBLANK('Table_Name'[Number]), BLANK(), CALCULATE(COUNT('Table_Name'[Number]), ALLEXCEPT('Table_Name', 'Table_Name'[Number])))

 

Outcome: 

 

I want 0 instead of odd number (like 813).

 

Thank you in advance.

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Anonymous 

    You can create a measure, if an ID is repeated show the count of the ID otherwise return 0.

     

    CountIfRepeat = IF(COUNT([ID])>1,CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[ID]=MAX([ID]))),0)
     

     

     

    Best regards
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Try:

    # of Extensions = 
  VAR __Number = 'Table_Name'[Number]
  VAR __Count = COUNTROWS(FILTER(ALL('Table_Name'),[Number]=__Number))
RETURN
  SWITCH(TRUE(),
    ISBLANK(__Number),BLANK(),
    ISBLANK(__Count),0,
    __Count
  )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Greg_Deckler Thank you for the quick reply. Unfortunately, this isn't working either.

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        Anonymous I'm confused as to you setup. Do you have a Name and Number column? Maybe something like:

        # of Extensions = 
  VAR __Number = 'Table_Name'[Number]
  VAR __Name = 'Table_Name'[Name]
  VAR __Count = COUNTROWS(FILTER(ALL('Table_Name'),[Name]=__Name))
RETURN
  SWITCH(TRUE(),
    ISBLANK(__Number),BLANK(),
    ISBLANK(__Count),0,
    __Count
  )

        Can you provide sample data as text to mock this up?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Anonymous 

    You can create a measure, if an ID is repeated show the count of the ID otherwise return 0.

     

    CountIfRepeat = IF(COUNT([ID])>1,CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),[ID]=MAX([ID]))),0)
     

     

     

    Best regards
    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.