Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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 @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.
Your sample data does not seem to have reliably sortable columns, so RANKX doesn't seem to be applicable.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |