Need Help on Dax to achieve this logic

[vengadesh_p]

Hi All,

I have data like below table 

Year	Name	Is Active
2018	ABC	NO
2018	ABC	YES
2020	ABC	YES
2021	ABC	YES
2022	ABC	NO

i want to show this information in pivot table

if person has two different status in same year we want to consider Yes condition & if we dont have record for any year we want to consider as NO Status

Expected Result 

Ex: 2018 we have two status (Yes\No) so we want to consider Yes
2019 we dont have any record so we want to consider NO

How Can i Achive this logic in DAX?

Thanks

[vengadesh_p]

Thanks @Jihwan_Kim 
its working if we have only one user, in my data i have more than 2 user, what i want to update in DAX
for 2018 its not show any data

Data:  

Year	Name	Is Active
2018	ABC	NO
2018	ABC	YES
2020	ABC	YES
2021	ABC	YES
2022	ABC	NO
2018	XXX	NO
2018	XXX	YES
2020	XXX	YES
2021	XXX	NO
2022	XXX	YES

[Jihwan_Kim]

Hi,

Thank you for your message.

May I ask how do you want to see for the two users?

If it is OK with you, please kindly describe how the expected outcome looks like for the changed sample. It helps a lot to fix the measure and create a more accurate measure.

Thank you.

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Expected outcome measure: =
IF (
    HASONEVALUE ( Active[Is Active] ) && HASONEVALUE ( 'Year'[Year] ),
    SWITCH (
        TRUE (),
        CALCULATE ( COUNTROWS ( Data ), ALL ( Active ) ) = 0, IF ( SELECTEDVALUE ( Active[Is Active] ) = "NO", 1 ),
        CALCULATE ( COUNTROWS ( Data ), ALL ( Active ) ) = 1, COUNTROWS ( Data ),
        CALCULATE ( COUNTROWS ( Data ), ALL ( Active ) ) = 2, CALCULATE ( COUNTROWS ( Data ), KEEPFILTERS ( Data[Is Active] = "YES" ) )
    )
)