Forum Discussion

GuestUser123's avatar
GuestUser123
Regular Visitor
4 years ago
Solved

Comparison of First Index with the Second one

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...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

JirkaZ's avatar
JirkaZ
Icon for Solution Specialist rankSolution Specialist
4 years ago

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's avatar
GuestUser123
Regular Visitor
4 years ago

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's avatar
    JirkaZ
    Icon for Solution Specialist rankSolution Specialist
    4 years ago

    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's avatar
      GuestUser123
      Regular Visitor
      4 years ago

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

      • JirkaZ's avatar
        JirkaZ
        Icon for Solution Specialist rankSolution Specialist
        4 years ago

        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.