Forum Discussion

JCuoco's avatar
JCuoco
Regular Visitor
4 years ago

Creating New Value from Previous Values

I am looking to create a new measure/column from a previous field that has more than one value in it.

 

We have a field that outlines two different types of visit types and i want to see which individuals have had BOTH types.

 

My goal is to have users who have had both types in their own value, while anyone else who has only had one of the two be labled as another value.

 

The two visit types are Dental/Primary  - so if someone had both visit types, it would say "both" 

 

I hope this isnt too confusing, if so, i will answer any questions. 

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi JCuoco 

    what exactly do you mean by

    "while anyone else who has only had one of the two be labled as another value."?

    Basically you csn create a new column using simple IF statment(s) to achieve the desired results. 

    • JCuoco's avatar
      JCuoco
      Regular Visitor
      4 years ago

      Would i use an "and" statement to grab both? 

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        JCuoco 
        can please share some sample data or schreenshots explaining how the data looks like and what is the expected output.

  • JCuoco's avatar
    JCuoco
    Regular Visitor
    4 years ago

     

     

    Below is a fake data set, as i work in healthcare, but this will be helpful still.

     

    Patients 1-12 have visits at primary but patients 1-3 also have speciality visits ..... i would want the patients with both values to be called "both" and while the indivudals with one type of facility is just labeled as "one" in a new column. 

     

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      JCuoco 

      Try

      Class =
IF (
    COUNTROWS ( SUMMARIZE ( Table, Table[Patient], Table[Type of Facility] ) ) = 1,
    "One",
    "Both"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi JCuoco ,

      You can create a calculated column as below to get it:

      Column = 
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Type of Facility] ),
        FILTER ( 'Table', 'Table'[Patient] = EARLIER ( 'Table'[Patient] ) )
    )
RETURN
    IF ( NOT ( ISBLANK ( _count ) ), IF ( _count = 1, "One", "Both" ), BLANK () )

      Best Regards

      • JCuoco's avatar
        JCuoco
        Regular Visitor
        4 years ago

        appreciate the help but this didnt work - i think i know why it didnt, as i am using two separate data sets. I probably should have mentioned that previosly. Column A and B are in different sets. 

        Apologies for that