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())