Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Copy unique Numbers

Hi,   I got a problem. I have a table with a few columns. my table has more than 300.000 lines. In one column are delivery notes, many of them exist more than one time in the column. I want to ...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    5 years ago

    Hi Anonymous 

     

    Let me know whether this helps:

    Column = var a = COUNTROWS(FILTER(ALL('Table'),[Delivery notes]=EARLIER('Table'[Delivery notes])&&[Index]<=EARLIER('Table'[Index])))
return
IF(a =1 , 'Table'[Delivery notes],BLANK())
    Column 2 = IF([Column]=BLANK(),BLANK(),[Liter])

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Anonymous Do you have an Index or something to denote "before"? Do you want DAX or Power Query or doesn't matter? If DAX and you have an index or date, you could use a variation of MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  IF(ISBLANK(__Previous),__Current,BLANK())

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Greg_Deckler thank you for your replay as well.

    No i dont have an index. I have column with date but every date exist more than 500 times.
    It doesn't matter how this will be fixed. But i cant find the relation between your solution and my table.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      Anonymous Can you just add an Index column in Power Query Editor? Need something to define "previous"

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Greg_Deckler,

         

        yes i can.