DAX Count Only the first occurence of a value

[harvis]

Hi,

I have a large dataset containing numbers that can be duplicate by date, month and so on. The numbers have an unique ID. I simplified the dataset here

The last number in uniqueid is not imcremental in mye real dataset. The dates is usually different as well.

What I want to do is to count the first (or one of the occurence) of the number column where there are repeating numbers. Then I need to get the row count as well as total correct. In this example the total should be 2 (the first row (or one of them) of the 123 number, the same for 666 number).

I have tried several formulas, the formulas in the picture underneath is

fcount = COUNT(samplenumbers[numbers])

distinctcount = DISTINCTCOUNT(samplenumbers[numbers])

repeatingcount = CALCULATE(COUNT(samplenumbers[numbers]);ALL(samplenumbers);VALUES(samplenumbers[numbers]);VALUES(Dato))

repeatingsum=
SUMGjentagendeKunderTotalSum = IF([GjentagendeKunderALL]>1;1;BLANK())

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

=CALCULATE(COUNTROWS(samplenumbers),FILTER(SUMMARIZE(VALUES(samplenumbers[numbers]),[numbers],"ABCD",COUNTROWS(samplenumbers)),[ABCD]>1))

Hope this helps.

[harvis]

Thank you, this worked well. The only change i did was changing the first COUNTROWS to DISTINCTCOUNT.

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Try using a COUNTROWS over a SUMMARIZE. If you only want rows that have more than one row, you could use a COUNT in your SUMMARIZE and use a FILTER on it before passing it to COUNTROWS.