CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Tom O
Tom O

Reputation: 2645

Count unique values of filtered table

I'd like to compute how many unique customerskeys I have within a certain date range.

This is the table I am working in:

Start         End    CustomersKeys
1-Jan-18    1-Feb-18    ?
1-Jan-18    1-Mar-18    ?
1-Jan-18    1-Apr-18    ?
1-Jan-18    1-May-18    ?

This is the table I need data from:

Date    CustomerKey
4-Feb-18    1
6-Feb-18    1
8-Apr-18    1
9-Apr-18    2
10-Apr-18   3

And this is what I want to end up with:

Start         End    Customers
1-Jan-18    1-Feb-18    0
1-Jan-18    1-Mar-18    1
1-Jan-18    1-Apr-18    1
1-Jan-18    1-May-18    3

I have tried a ton of different combinations of; COUNTROWS, FILTER, DISTINCTCOUNT, CALCULATE, DISTINCT, ALL, etc. But I keep running into errors. Advice is much appreciated.

Upvotes: 1

Views: 137

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

Try someting like this as a New Column:

Customers =
CALCULATE (
    DISTINCTCOUNT ( 'data'[CustomerKey] ),
    FILTER (
        'data',
        'data'[Date] >= 'DateRanges'[Start].[Date]
            && 'data'[Date] < 'DateRanges'[End].[Date]
    )
)

enter image description here

Upvotes: 4

Related Questions