Conditional Index column in DAX

[21818]

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 data source, so in that case Power Query is not an option and only DAX should be used:

date_column	code	Name	numdays	ID
01-Jan-22		John	0
02-Jan-22		John	1	ID1
03-Jan-22		John	2	ID1
04-Jan-22		John	3	ID1
05-Jan-22	A	John	0
07-Jan-22		John	1	ID2
07-Jan-22		John	2	ID2
07-Jan-22		Mark	1	ID3
08-Jan-22		Mark	2	ID3
09-Jan-22		Mark	0
12-Jan-22		Mark	1	ID4

First 4 columns are already created and I would like to create a new ID column based on the following:

- A new ID value should be created for each user starting on numday = 1. 

- The same ID should be used for the following consecutive days for the same user as long as the code is not a specific value (A) or numdays is not 0.

Thanks.

[v-yangliu-msft]

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.

[v-yangliu-msft · Accepted solution]

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

[21818]

Many thanks @v-yangliu-msft , 

When trying this solution I am stuck at the creation of the rank column as it is taking a long time without success. My current dataset has around 400K columns and I assume this could be the issue for this. 

[lbendlin]

Your sample data does not seem to have reliably sortable columns, so RANKX doesn't seem to be applicable.