Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
filarap
Helper III
Helper III

Countif in DAX or Power Query

‎08-13-2019 04:02 AM

Hi all,

 

Please help as i was not able to find solution for what i require.
I have dataset with repeating ID number and i need to count how many times it repeats in a dataset. Groupby does not work, as i need data to remain ungrouped.

 

My goal is to count how many ids i have that apear more thatn one time in a table. Preferably i would like to do it in DAX, but if i can create this calculation in power query, i will be able to easily do it in DAX.

 

Below is example of dataset i have.

 

What I have

What I need

ID

Countif

1

3

2

2

1

3

3

1

4

1

5

1

6

1

1

3

2

2

 

Any help is greatly appreciated

Regards

Filarap

 

Labels:
Message 1 of 11
961 Views
0
1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar
In response to filarap

‎08-13-2019 06:20 AM

If all you need is a measure for a card that displays the count of IDs that appear 2+ times, that should be pretty easy.  I'm not sure why you're so against using summarization or groupings for a single measure, but here's how I would do it:

CountMultiIDs = COUNTROWS(FILTER(ADDCOLUMNS( VALUES('Table'[ID]), "IDCount", CALCULATE(COUNTROWS('Table'))), [IDCount]>1))

The tricky bit here is to use CALCULATE around the inner COUNTROWS so that the DAX re-evaluates the context at which it is counting.

View solution in original post

Message 6 of 11
911 Views
0
10 REPLIES 10

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All