Comparison of First Index with the Second one

[GuestUser123]

Hello Everyone,

I have a huge dataset with me wherein I have multiple Customer IDs and each ID has multiple entries associated with them. Some IDs have only 1 entry, hence they have only 1 Index and some have multiple entries thereby having multiple indexes (1,2,3, etc for each entry).

I want to consider only those IDs that have more than 1 index so that I could do a comparison between the 1st and 2nd row in order to check the value of a Payment field so as to know if there is any change in the Payment field value from Index 1 to Index 2. 

However, I am not able to implement this scenario. Below is the snapshot of the scenario.

Please help. 

Thank you.

 Example of the scenario

[v-yanjiang-msft · Accepted solution]

Hi @GuestUser123 ,

According to your description, here's my solution.

My sample data is the same with yours.

Create a measure.

Check = 
IF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[CustomerID] = MAX ( 'Table'[CustomerID] )
                && 'Table'[Index] = 1
        ),
        'Table'[Payment]
    )
        = MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[CustomerID] = MAX ( 'Table'[CustomerID] )
                    && 'Table'[Index] = 2
            ),
            'Table'[Payment]
        ),
    "Not change",
    "Change"
)

 Get the expected result.

I attach my sample below for reference.

Best Regards,

Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GuestUser123]

Hi @JirkaZ  Thanks for the reply. However, I have only one table with me having all the above fields and I have to compare the first row with the second one to see if there has been any change in the Payment method.

[JirkaZ]

But you know you can abstract some other table from this one and do a proper modelling with many to one relationships etc., right?

[GuestUser123]

Yes I can do that. However, could you please elaborate a bit more that even if I create another table from this one, do the data modelling as well.. But how will I pull only those rows where Index is more than 1 so that I could do a comparison on the Payment method for each Cust ID?

[JirkaZ]

as I said before - then you can loop through the master customer table and evaluate the ROWCOUNT of the RELATEDTABLE to see if the customer has multiple indexes.

[GuestUser123]

Hi @JirkaZ Is there a way to use an IF Condition in this scenario?

[JirkaZ]

Yes, but you should first understand the concept and why you should normalize your model, what results would the countrows(relatedtable) give you and how to use it in your scenario. 

[JirkaZ]

It's really hard to tell what the outcome of you checks should be. I assume you have a master Customers table somewhere in your data model... so you could loop through the customers table and evaluate the row count of the related table (the one pictured) and then perform the operations you want.