Forum Discussion

21818's avatar
21818
Frequent Visitor
4 years ago
Solved

Conditional Index column in DAX

Hello,  I need to create a new index column based on values from previous rows and I would appreciate some help here.  This is an example of the type of data we have. We don't have access to the da...
conditional column
DAX
previous rows
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  21818 ,

    Here are the steps you can follow：

    1. Create calculated column.

    rand =
FORMAT(
RAND(),"General Number")
    Rank =
  VAR __Item =[rand]
  VAR __Text = CONCATENATEX('Table',[rand],"|")
  VAR __Count = PATHLENGTH(__Text)
  VAR __Table =
    ADDCOLUMNS(
     GENERATESERIES(1,__Count,1),
      "__Item",PATHITEM(__Text,[Value])
    )
  VAR __TableFinal =
    SUMMARIZE(__Table,[__Item],"Index",MINX(FILTER(__Table,[__Item]=EARLIER([__Item])),[Value]))
RETURN
  MINX(FILTER(__TableFinal,[__Item] = __Item),[Index])
    ID1 =
IF(
    'Table'[numdays]=0 ||'Table'[code] = "A",BLANK(),
  "ID"&""&  RANKX(FILTER(ALL('Table'),'Table'[numdays]=EARLIER('Table'[numdays])),[Rank],,ASC))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  21818 ,

For large amounts of data:

I'd like to suggest you follow the optimization guide for Power BI in the document . You may also use Performance Analyzer to examine report element performance.

You can also perform an optimization on dax:

https://maqsoftware.com/insights/dax-best-practices

 

Also consider turning off the following actions in Option:

Current File->Data Load->Auto Date/Time

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.