DAX: Distinctcount with two filters on same column

[Anthonsen]

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]

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]

Hi

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

 

[Ozeroth]

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]

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]

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]

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