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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.