Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
21818
Frequent Visitor

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_columncodeNamenumdaysID
01-Jan-22 John0 
02-Jan-22 John1ID1
03-Jan-22 John2ID1
04-Jan-22 John3ID1
05-Jan-22AJohn0 
07-Jan-22 John1ID2
07-Jan-22 John2ID2
07-Jan-22 Mark1ID3
08-Jan-22 Mark2ID3
09-Jan-22 Mark0 
12-Jan-22 Mark1ID4

 

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.

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1652429668483.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_0-1652776458115.png

 

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
Community Support
Community Support

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:

vyangliumsft_0-1652429668483.png

 

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. 

lbendlin
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.