Help needed with DAX

[Anonymous]

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 · Accepted solution]

@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.

[Greg_Deckler]

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

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

[Greg_Deckler]

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

@Greg_Deckler 

 I have a table with ID and Extension ID. I want to calculated how many times an extension was filed for each ID. If no extension is filed, then it should retun 0 else return the total number of extensions for each ID.

Firstly, I applied a conditional formatting in Power Query Editor - if Extension Id is blank, then it should return 0 else return ID (see the table)

In table visualization, I want the number of extensions for each ID. So, it should reflect something like this 

I hope this sample is easier to understand. Any help would be appreciated. Thank you.

[Greg_Deckler]

@Anonymous Text data please.

[Anonymous]

@Greg_Deckler 

Power Query Editor
ID	Extension ID	Conditional Column
A1		0
B2		0
C3	11	C3
C3	12	C3
D4		0
E5	13	E5
E5	14	E5
E5	15	E5
F6		0
G7	16	G7
G7	17	G7

Conditional column is formatted field - if Extenson ID is 0 then 0 else (select column) ID

Desired Output
ID	# of Extensions
A1	0
B2	0
C3	2
D4	0
E5	3
F6	0
G7	2