# DAX: Distinctcount with two filters on same column



## Anthonsen (Jun 11, 2015)

Hi.

I have a table with two columns - "Name" and "Year". 
There are several rows with same name and year, and there are several rows with same name and different year.

I would like to Count names, where there are at least one row with the name and year = 2014 and one row with the name and year = 2015. This should be counted as one name. 

Is that possible using e.g. Calculate(DISTINCTCOUNT(NAME);Filter(???))

Looking so much forward to your answer.

Thanks.


----------



## Kazlik (Jun 11, 2015)

If your data was the following would you expect a count of 2 or 3 or something else?

A	2015
A	2014
B	2015
B	2015
B	2015


----------



## Anthonsen (Jun 11, 2015)

Hi

I expect the count to be one, as only A have both year 2014 and 2015


----------



## Ozeroth (Jun 11, 2015)

The logic I would follow is that you need to filter the table twice, for Names with Year=2014 & for Names with Year=2015. Then the intersection of these two filters becomes the filter for your CALCULATE(DISTINCTCOUNT...

Here is one possible approach using CALCULATETABLE for the two year filters, but I'm sure you could use FILTER as well:

```
=
CALCULATE (
    DISTINCTCOUNT ( Table1[Name] ),
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        Table1[Year] = 2014
    ),
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        Table1[Year] = 2015
    )
)
```


----------



## Anthonsen (Jun 12, 2015)

Thank you - it Works 

I have two additional questions:

I would like to replace "2014" and "2015" with two columns in the tabel, where the calculations are made. 
And I would like to add a dimention (quater), så it should be year = 2014 and quater Q1 compared to year = 2015 and quater Q1. 

The data looks like this:

 A 2015 Q1
 A 2014 Q1
 B 2015 Q1
 B 2015 Q1
 B 2015 Q2

And the calculation table lookslike this:

Year1  Year 2  Quarter  Calculation
2014   2015    Q1         ....
2014   2015    Q2         ....
2014   2015    Q3        .....

Thansk in advance.

Do I need to do anything, now I have the answer I asked for?


----------



## Ozeroth (Jun 12, 2015)

Hmm...would like to understand your overall model a bit better.

However, from what you've described, we can follow similar logic to before, but using a model with a few relationships set up and a calculated column.

(I'm not sure if a _measure_ rather than a calculated column could make sense for what you're doing...)

1. Add these tables to the Data Model:
*Data - *your data table (Name/Year/Quarter)
*Year**- *Table listing all years
*Quarter - *Table listing all quarters
*Calc - *your condition table with Year1/Year2/Quarter

2. Create relationships
Data[Year] -> Year[Year]
Data[Quarter] -> Quarter[Quarter]
Calc[Year1] -> Year[Year]
Calc[Year2] -> Year[Year] _(inactive relationship)
_Calc[Quarter] -> Quarter[Quarter]

3. Then add this calculated column to Calc

```
=
CALCULATE (
    DISTINCTCOUNT ( Data[Name] ),
    CALCULATETABLE ( VALUES ( Data[Name] ) ),
    CALCULATETABLE (
        VALUES ( Data[Name] ),
        USERELATIONSHIP ( Calc[Year2], Year[Year] )
    )
)
```

Explanation: Similar idea to before, the two CALCULATETABLEs produce a list of Names which match the current row of the *Calc *table. The first CALCULATETABLE uses Year1 (active relationship) and the second uses Year2 (inactive relationship). The intersection of the two lists becomes the filter context for the DISTINCTCOUNT.


----------



## Anthonsen (Jun 15, 2015)

Awesome - thanks a lot. 
Do I need to do anything, now this is solved?


----------

