Forum Discussion

giordafrancis's avatar
giordafrancis
Frequent Visitor
5 years ago
Solved

Using Dax only create an index column

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 ...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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/

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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]
                )
            )
    )