Creating New Value from Previous Values

[JCuoco]

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. 

[JCuoco]

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. 

[v-yiruan-msft]

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]

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

[v-yiruan-msft]

Hi @JCuoco ,

Could you please some sample data from those two "data sets" which column A and B located in? Is there any relationship between these two "data sets"? If yes, please provide the relationship info. It is better if you can share a simplified pbix file with me. You can refer the following thread to upload your file in the community. Thank you.

How to upload PBI in Community

Best Regards

[tamerj1]

@JCuoco 

Would you please provide more details about your data?

[tamerj1]

@JCuoco 

Try

Class =
IF (
    COUNTROWS ( SUMMARIZE ( Table, Table[Patient], Table[Type of Facility] ) ) = 1,
    "One",
    "Both"
)

[tamerj1]

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]

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

[tamerj1]

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