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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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.

 

B_H_M_1-1635341336461.png

 

Formula used : 

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

 

Outcome: 

B_H_M_2-1635341449746.png

 

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

 

Thank you in advance.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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)
 
Vpazhenmsft_0-1635831909699.png

 

 

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.

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@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)
 
Vpazhenmsft_0-1635831909699.png

 

 

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
Community Champion
Community Champion

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

B_H_M_0-1635344771217.png

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

B_H_M_0-1635350769906.png

 

 

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

B_H_M_1-1635350769905.png

 

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

@Anonymous Text data please.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 

 

Power Query Editor
IDExtension IDConditional Column
A1 0
B2 0
C311C3
C312C3
D4 0
E513E5
E514E5
E515E5
F6 0
G716G7
G717G7

 

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

 

Desired Output
ID# of Extensions
A10
B20
C32
D40
E53
F60
G72

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.