Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to identify if value exists somewhere else in Excel table?

Hello,

I have the below data table in PBI. I need to find a way to identify how many Persons have multiple Types.

For example what is the # of Persons that have a 405 type and 406 type? I want to aggregate this for every Type (405,406,407,408).

 

Maybe four columns titled "In 405?", "In 406?", "In 407?", "In 408?" for each row that has Yes/No values.



Person       Type

Person 1405
Person 1406
Person 2406
Person 2407
Person 2408
Person 3407
Person 3408
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    In Dax, create calculated columns

    405 = CONTAINS('Table','Table'[Type],405,'Table'[Person],'Table'[Person])
     
    406 = CONTAINS('Table','Table'[Type],406,'Table'[Person],'Table'[Person])
    407 = CONTAINS('Table','Table'[Type],407,'Table'[Person],'Table'[Person])

    and so on...

     






1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    In Dax, create calculated columns

    405 = CONTAINS('Table','Table'[Type],405,'Table'[Person],'Table'[Person])
     
    406 = CONTAINS('Table','Table'[Type],406,'Table'[Person],'Table'[Person])
    407 = CONTAINS('Table','Table'[Type],407,'Table'[Person],'Table'[Person])

    and so on...