topic Re: Conditional Index column in DAX in DAX Commands and Tips

Conditional Index column in DAX

21818 — Tue, 10 May 2022 14:06:45 GMT

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.

Re: Conditional Index column in DAX

lbendlin — Thu, 12 May 2022 01:13:42 GMT

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

Re: Conditional Index column in DAX

Anonymous — Fri, 13 May 2022 08:15:35 GMT

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

Re: Conditional Index column in DAX

21818 — Tue, 17 May 2022 05:48:56 GMT

Many thanks Anonymous ,

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.

Re: Conditional Index column in DAX

Anonymous — Tue, 17 May 2022 08:34:42 GMT

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.