Count unique values when another column has one value but not another value

[alexsimpson]

Hi, I have a data set similar to below:

Name	Income Type	Amount
Alex	Initial	100
John	Initial	100
Sam	Ongoing	100
Alex	Initial	100
John	Ongoing	100
Sam	Ongoing	100

I am trying to create a measure that counts the number of people that just have 'Initial' i.e. have initial but don't have ongoing. Therefore on the above, it would be 1 which would be Alex. 

Any help would be much appreciated.

Many thanks in advance,

Alex

[tex628 · Accepted solution]

Measure = 
IF(
CALCULATE(DISTINCTCOUNT(Table1[Column]) = 1),
    CALCULATE(DISTINCTCOUNT(Table1[Column]), Table1[Income Type] = "Initial"), BLANK()
)

Table1[Column] being a Calculated column = [Name] & [Income Type] 

[v-yuta-msft · Accepted solution]

@tex628 ,

The solution of Tex is simple and great. In addtion, you can also create a calculate column [Flag]:

Flag = 
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Income Type] ),
        ALLEXCEPT ( 'Table', 'Table'[Name] )
    ) = 1
        && CALCULATE (
            DISTINCTCOUNT ( 'Table'[Name] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Name] ),
                'Table'[Income Type] = "Initial"
            )
        ) = 1,
    1,
    0
)

Then create a measure which may also achieve the result:

Count = CALCULATE(DISTINCTCOUNT('Table'[Name]), FILTER('Table', 'Table'[Flag] = 1))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628 · Accepted solution]

Measure = 
IF(
CALCULATE(DISTINCTCOUNT(Table1[Column]) = 1),
    CALCULATE(DISTINCTCOUNT(Table1[Column]), Table1[Income Type] = "Initial"), BLANK()
)

Table1[Column] being a Calculated column = [Name] & [Income Type]