Forum Discussion
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 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.
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
4 Replies
- lbendlin
Super User
Your sample data does not seem to have reliably sortable columns, so RANKX doesn't seem to be applicable.
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
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.
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.
