cancel
Showing results for
Did you mean:
Regular Visitor

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

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.

Alex

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User II

## Re: Count unique values when another column has one value but not another value

```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]

Community Support Team

## Re: Count unique values when another column has one value but not another value

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.

2 REPLIES 2
Super User II

## Re: Count unique values when another column has one value but not another value

```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]

Community Support Team

## Re: Count unique values when another column has one value but not another value

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.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.