Using Dax only create an index column

[giordafrancis]

Consider Table1 below , also note I'm not able to use Power Query currently for below report.

user_key	FYear
001	14/15 Q4
002	15/16 Q1
003	15/16 Q1
004	15/16 Q2

I want to create a new table (Table2) with the format below. It will capture all discint values from Table1[FYear] column.
@ddecker

FYear	idx
14/15 Q4	1
15/16 Q1	2
15/16 Q2	3

[Jihwan_Kim · Accepted solution]

Hi, @giordafrancis 

I only know how to create this table in two steps. I could not find a way to create it in one step.

step 1. create a Table

new table = SUMMARIZE('Table', 'Table'[FYear])

Step 2. create a calculated colum

idx =
CALCULATE (
SUMX ( 'new table', 1 ),
FILTER ( 'new table', 'new table'[FYear] <= EARLIER ( 'new table'[FYear] ) )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

@giordafrancis, @Jihwan_Kim 

Here's how to do it in one go:

[New Table] =
    addcolumns(
        distinct( YourTable[FYear] ),
        "idx",
            var CurrentFYear = YourTable[FYear]
            return
            countrows(
                summarize(
                    filter(
                        YourTable,
                        YourTable[FYear] <= CurrentFYear
                    ),
                    YourTable[FYear]
                )
            )
    )

[giordafrancis]

@amitchandak @Jihwan_Kim @Anonymous