Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with if statement for different values in other columns

I have a dataset below:   Location Name age income UK x teen low UK x adult low UK y teen high USA z adult low USA z adult high   I want to point out mistak...
Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution. Create a measure.

    Discrepancy found? =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Location] = MAX ( 'Table'[Location] )
                && 'Table'[Name] = MAX ( 'Table'[Name] )
                && (
                    'Table'[age] <> MAX ( 'Table'[age] )
                        || 'Table'[income] <> MAX ( 'Table'[income] )
                )
        )
    ) > 0,
    "Yes",
    "No"
)

    Get the correct result:

    I attach my sample below for your 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.

ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
3 years ago

Anonymous ,

 

Create a Calculated Column as shown below...

 

Discrepancy =

IF (
    CALCULATE (
        COUNT ( 'Table'[income] ),
        FILTER (
            'Table',
            'Table'[Location] = EARLIER ( 'Table'[Location] )
                && 'Table'[Name] = EARLIER ( 'Table'[Name] )
        )
    ) > 1,
    IF (
        DISTINCTCOUNT ( 'Table'[age] ) > 1
            || DISTINCTCOUNT ( 'Table'[income] ) > 1,
        "Y"
    ),
    "N"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ddpl,

     

    Thanks for submitting this, unfortunately it doesn't work as it doesn't fulfil the requirement of being able to indicate which ones have the same location & name, but different values for income or age.

     

    If it helps, there will only ever be "teen" or "adult" values for age, and with Income it will only be "High", Low" or "N/a"

    • ddpl's avatar
      ddpl
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Anonymous ,

       

      can you share slightly more data with expected result.