Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 :
Outcome:
I want 0 instead of odd number (like 813).
Thank you in advance.
Solved! Go to Solution.
@Anonymous
You can create a measure, if an ID is repeated show the count of the ID otherwise return 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.
@Anonymous
You can create a measure, if an ID is repeated show the count of the ID otherwise return 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.
@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 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?
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.
@Anonymous Text data please.
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 |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |