Distinct count only if all values in another column are met

[Abdulazizmq_16]

Dears, 

I would like to distinct count values in the standard column on multiple conditions in another compliance status column. 

I have 4 compliance status: complianced, partly complianced, not complianced, NA

Condition 1:  distinct count standard as complianced if all values in compliance status is only complianced 

Condition 2:  distinct count standard as partly complianced if values in compliance status are mix of complianced, partly complianced and/or not complianced

Condition 3:  distinct count standard as not complianced if values in compliance status is only not complianced

Condition 4:  distinct count standard as NA if all values in compliance status is only NA

Standard	Standard requirements	Compliance status
5.1.1	5.1.1.1	complianced
5.1.1	5.1.1.2	complianced
5.1.2	5.1.2.1	complianced
5.1.2	5.1.2.2	partaly complianced
5.1.2	5.1.2.3	not complianced
5.1.2	5.1.2.4	complianced
5.1.3	5.1.3.1	not complianced
5.1.3	5.1.3.2	not complianced
5.1.4	5.1.4.1	NA
5.1.4	5.1.4.2	NA
5.1.5	5.1.5.1	complianced

example above there are 5 standards,

# of complianced is 2

# of partaly complianced is 1

# of not complianced is 1

# of NA is 1

[jdbuchanan71]

I would do a measure that is ONLY Partly Complianced because that is one of your distinct statuses

Do one that is "mixed" (a combination, excluding NA).

Then add those together.

I added some to the example, where doe 5.1.8 belong because it has partial and NA?

I attached my sample for you to look at.

[Abdulazizmq_16]

It's my mistake, when a standard is NA, that means all standard's details are NA, 

So if 5.1.8 is NA, all details standard (5.1.8.1, 5.1.8.2) are NA

Also 5.1.2.4 I changed it to complianced 

[jdbuchanan71]

So Partly Compliance does not include "NA"?  It is the combiniation of those that are only "Partly Complianced" and those that are a mix of "* compliance"?

Where do you count one that has NA and Partial Compliance?

[Abdulazizmq_16]

Yes, does not include NA, 

Partly Complianced could be one of these conditions if standard requirements:

1- mix of complianced & not complianced 

2- mix of complianced & partly complianced 

3- mix of not complianced & partly complianced 

4- only partly complianced 

5- mix of complianced & partly complianced  & not complianced 

[jdbuchanan71 · Accepted solution]

@Abdulazizmq_16 

Give these measures a try.

Complianced = 
VAR _Other = CALCULATETABLE( DISTINCT ( 'Your Table'[Standard] ), 'Your Table'[Compliance status] <> "complianced" )
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( 'Your Table'[Standard] ), 
        NOT ( 'Your Table'[Standard] IN ( _Other ) )
    )

NA = 
VAR _Other = CALCULATETABLE ( DISTINCT ( 'Your Table'[Standard] ), 'Your Table'[Compliance status] <> "NA" )
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( 'Your Table'[Standard] ), 
        NOT ( 'Your Table'[Standard] IN ( _Other ) )
    )

Not Complianced = 
VAR _Other = CALCULATETABLE ( DISTINCT ( 'Your Table'[Standard] ), 'Your Table'[Compliance status] <> "not complianced" )
RETURN
    CALCULATE ( 
        DISTINCTCOUNT ( 'Your Table'[Standard] ), 
        NOT ( 'Your Table'[Standard] IN ( _Other ) )
    )

Partly Complianced = 
COUNTROWS (
    FILTER (
        VALUES ( 'Your Table'[Standard] ),
        CALCULATE (
            COUNTROWS (
                SUMMARIZE (
                    'Your Table',
                    'Your Table'[Standard],
                    'Your Table'[Compliance status]
                )
            )
        ) > 1
    )
)

[Abdulazizmq_16]

That code worked for partly complianced

(COUNTROWS (
    FILTER (
        VALUES ( 'Your Table'[Standard] ),
        CALCULATE (
            COUNTROWS (
                CALCULATETABLE(
                    SUMMARIZE (
                        'Your Table',
                        'Your Table'[Standard],
                        'Your Table'[Compliance status]
                    ),
                    'Your Table'[Compliance status] <> "NA"
                )
            )
        ) > 1
    )
))
+
(
    VAR _Other = 
    CALCULATETABLE(
        DISTINCT('Your Table'[Standard]),
        'Your Table'[Compliance status] <> "not complianced"
        )
RETURN
CALCULATE(
    DISTINCTCOUNT('Your Table'[Standard]),
        NOT('Your Table'[Standard] IN (_Other))
    )
)

Note: NA must be applied to all standard requirements, other ways it would not be counted

[Abdulazizmq_16]

Thank you so much. all of them worked properly except partly complianced doesn't work.

Note: it could be a mix of:

1- complianced & not complianced 

2- complianced & partly complianced 

3- not complianced & partly complianced 

4- only partly complianced 

5- complianced & partly complianced  & not complianced