DAX to show the rows when we have the status is only "True"

[Anonymous]

Hi, I have a table like below where we have 3 columns but for some people in Name has the status both True and False. However, there are people who're status is only True. Now I want to write a DAX to show who are those people who has the status is only True and another Dax to show who are the people who have both the status.

Can someone please help me with this?

Country	Name	Status
India	Amit	TRUE
India	Amit	FALSE
USA	John	TRUE
USA	John	FALSE
UK	Chris	TRUE
UK	Chris	FALSE
India	Suresh	TRUE
India	Naresh	TRUE
UK	Smith	TRUE
USA	Ashish	TRUE

[MFelix · Accepted solution]

Hi @Anonymous ,

Depending on what you need you can do a measure like the following:

Only true =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table';
            'Table'[Name];
            "@count"; COUNTA ( 'Table'[Status] )
        );
        [@count] = 1
    )
)

Both States = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table';
            'Table'[Name];
            "@count"; COUNTA ( 'Table'[Status] )
        );
        [@count] = 2
    )
)

Check PBIX file attach.

[Anonymous]

Thanks @MFelix - It works!

[Greg_Deckler]

@Anonymous  - You should be able to use EXCEPT and INTERSECT. For example, something like this:

Measure 2 = 
    VAR __Name = MAX('Table (2)'[Name])
    VAR __True = SELECTCOLUMNS(FILTER('Table (2)',[Status]=TRUE()),"Name",[Name])
    VAR __False = SELECTCOLUMNS(FILTER('Table (2)',[Status]=FALSE()),"Name",[Name])
    VAR __Names = SELECTCOLUMNS(EXCEPT(__True,__False),"Name",[Name])
RETURN
    IF(__Name IN __Names,1,BLANK())

Note, this is an example of a Complex Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/td-p/1116633

[Anonymous]

Hi @Greg_Deckler, Thanks for your response. But, I'm getting blank.

[Greg_Deckler]

@Anonymous - Odd, did you put that measure in a table along with the Name column?

[Anonymous]

Yes! I did.