topic Re: Help needed with DAX in DAX Commands and Tips

Help needed with DAX

Anonymous — Wed, 27 Oct 2021 13:33:27 GMT

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.

Re: Help needed with DAX

Greg_Deckler — Wed, 27 Oct 2021 13:49:50 GMT

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 )

Re: Help needed with DAX

Anonymous — Wed, 27 Oct 2021 14:26:52 GMT

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

Re: Help needed with DAX

Greg_Deckler — Wed, 27 Oct 2021 14:43:11 GMT

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?

Re: Help needed with DAX

Anonymous — Wed, 27 Oct 2021 16:07:16 GMT

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.

Re: Help needed with DAX

Greg_Deckler — Wed, 27 Oct 2021 16:56:57 GMT

Anonymous Text data please.

Re: Help needed with DAX

Anonymous — Wed, 27 Oct 2021 17:32:00 GMT

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

Re: Help needed with DAX

Anonymous — Tue, 02 Nov 2021 05:46:11 GMT

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.