Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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())
Solved! Go to Solution.
Hi,
Try this measure
=CALCULATE(COUNTROWS(samplenumbers),FILTER(SUMMARIZE(VALUES(samplenumbers[numbers]),[numbers],"ABCD",COUNTROWS(samplenumbers)),[ABCD]>1))
Hope this helps.
Hi,
Try this measure
=CALCULATE(COUNTROWS(samplenumbers),FILTER(SUMMARIZE(VALUES(samplenumbers[numbers]),[numbers],"ABCD",COUNTROWS(samplenumbers)),[ABCD]>1))
Hope this helps.
Thank you, this worked well. The only change i did was changing the first COUNTROWS to DISTINCTCOUNT.
You are welcome.
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.
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |