Creating an Index for filtered data

[BaileyL]

Hello

Beginner question,

This the the filtered table that I currently use on a graph (X-ID, Y-M_DAYS_TO_COMPLETE). I am filtering by type, this table returns a specific one, which starts at 52495 and ends at 51692. I want the the X-axis to be an index of each row starting from 1, not the rows ID. 

1, 52495

2, 52497

3, 51259

4, 51389...etc 

Any ideas on how I can achieve this? 

Many thanks. 

[OwenAuger · Accepted solution]

Hi @BaileyL 

Here's an example of one approach (PBIX attached)

1. I loaded your sample data into a table called 'Data'.

2. Created an Index table with single column Index[Index].

The Index column contains integers from 1 to the distinct number of values of Data[ID] across the entire dataset (16 in this example but would actually be larger):

3. Created these measures:

M Days to Complete Sum = 
SUM ( Data[M_DAYS_TO_COMPLETE] )

M Days to Complete Sum by Index = 
VAR IDValuesAllselected =
    CALCULATETABLE (
        SUMMARIZE ( Data, Data[ID] ),
        ALLSELECTED ( )
    )
VAR IndexValues =
    VALUES ( 'Index'[Index] )
VAR Index_ID =
    GENERATE (
        IndexValues,
        INDEX ( 'Index'[Index], IDValuesAllselected )
    )
RETURN
    CALCULATE (
        [M Days to Complete Sum],
        Index_ID
    )

The 2nd measure determines the ID corresponding to the currently filtered Index using the INDEX function.

It then applies this as a filter to calculate the underlying measure.

This could also have been written another way by determining the indexes of all ID values, then selecting the ID(s) corresponding to the currently filtered Index(es)

4. Create visuals displaying the 1st measure by ID and the 2nd measure by Index:

Hopefully I've understood your requirements correctly. Please post back if needed! 🙂

Regards

[BaileyL]

Thank you for your help this works great. I hadn't thought of having a seperate Index table. 

Many thanks, 

Bailey.