Solved: Add index column with if statement

joooffice · ‎03-16-2021

I currently add an index column that pulls the starting figure from the results of another query called Last Submission ID which works fine.

= Table.AddIndexColumn(#"Sorted Rows", "Index", #"Last Submission ID"+1, 1)

Sometimes the value for #Last Submission ID = null and so it causes an error. When it is null the index should just start at 1

I've tried

= Table.AddIndexColumn(#"Sorted Rows", "Index", if #"Last Submission ID<>null then #"Last Submission ID"+1 else 1, 1)

but it doesnt work. I probably have the syntax wrong.

CNENFRNL · ‎03-16-2021

@joooffice , you might want to try

= Table.AddIndexColumn(#"Sorted Rows", "Index", (#"Last Submission ID"??0)+1, 1)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

CNENFRNL · ‎03-16-2021

@joooffice , you might want to try

= Table.AddIndexColumn(#"Sorted Rows", "Index", (#"Last Submission ID"??0)+1, 1)

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!