cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tegl
Frequent Visitor

Count number of Customers, that only have one Record - last 31 days

Hello PowerBi Guru's

 

A simple (I think) friday struggle 🙂

 

So, i'm using current Dax-measure to tell me, at all time in total, how many customers has only one record:

 

COUNTROWS(FILTER(VALUES('RecordTable'[customer_id]),
CALCULATE(COUNT('RecordTable'[Record_id])) = 1)
 
Now - I want to "filter" that measure, only showing how many of the customers with 1 record - in total(regardless of time) - have bought the last 31 days?.
 
If I put the filter inside the calculate filterexpression, like this:
COUNTROWS(FILTER(VALUES('RecordTable'[customer_id]),
                   CALCULATE(COUNT('RecordTable'[Record_id]),
                                              Datesbetween('RecordTable'[Record_time],Today()-31,Today()) = 1)

I will not get the correct result, as the Date-filtration is inside the calculate function, meaning that customers that have more than 1 record (but only 1 last 31 days), will be included.
 
Tried to add an "&&" after the calculate filter, but it returns an error saying multiple values was return, where a single value is expected.
 
Any Ideas on how to resolve this? 
 
Has to be a Dax-measure solution - slicer/filter filtration to get the specific result is not an option here.

 

1 ACCEPTED SOLUTION

@Tegl 

 

Have you tried that? It works well for me.

 

Download this file:https://gofile.io/d/YSn9AA

 

If it's possible, share a sample of your data.

 

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

Appreciate your Kudos!!

 

View solution in original post

9 REPLIES 9
VahidDM
Super User
Super User

Hi @Tegl 

 

Try this measure:

Measure = 
VAR _D =
    TODAY () - 31
VAR _A =
    FILTER (
        SUMMARIZE (
            FILTER ( RecordTable, RecordTable[Record_time] >= _d ),
            RecordTable[customer_id],
            "Count", COUNT ( RecordTable[Record_id] )
        ),
        [Count] = 1
    )
RETURN
    COUNTX ( _A, [Count] )

 

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

Appreciate your Kudos!!

 

Tegl
Frequent Visitor

Hi Vahid,

 

Yours seems to be very close, but still needs a minor adjustment i think.

Your measure returns me how many customers that have 1 record in the last 31days. 

 

But the result I am looking for is, how many customers with 1 record in total (regardless of time) - is in the last 31 days?

 

=> That will give me a smaller count, as your measure also inlcude those customers that might have more records outside the timeframe(last31days).

 

So for example 1 customer has 5 records 50 days ago and 1 record 20 days ago. That customer is include in your measure (as I see it) but shouldn't.

 

Hope it makes sence. 🙂

Hi @Tegl 

 

Try this:

Measure = 
VAR _D =
    TODAY () - 31
VAR _A =
    FILTER (
        FILTER (
            SUMMARIZE (
                RecordTable,
                RecordTable[customer_id],
                "Count", COUNT ( RecordTable[Record_id] ),
                "Max Date", MAX ( RecordTable[Record_time] )
            ),
            [Max Date]
                >= TODAY () - 31
                && [Max Date] <= TODAY ()
        ),
        [Count] = 1
    )
RETURN
    COUNTX ( _A, [Count] )

 

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

Appreciate your Kudos!!

 

Tegl
Frequent Visitor

Hi Again,

 

sry, but it dosen't seems to be the right approach.

 

In the first filter from the VAR_A table, you first filter on the dates, which leaves the same issue:

You get a table of customers and records for the last 31 days. Then you filter on the count.

 

But, I need table (Summarize) of all customers with only one record (regardsless of time) - and then - filter, counting how many of theese have a recordtime in the last 31days?.

 

(and.. The Max date seems to only bring you one record from each customers, although they might have more).

 

- Hope it makes sence - thanks for your effort 🙂

 

 

@Tegl 

 

Have you tried that? It works well for me.

 

Download this file:https://gofile.io/d/YSn9AA

 

If it's possible, share a sample of your data.

 

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

Appreciate your Kudos!!

 

Tegl
Frequent Visitor

Hi Again,

 

Yes - and found that I made an error implementing it in my file - everything looks great - thanks a lot for you help and effort..

Tegl_1-1634022722556.png

 

 

mcolb88
Helper III
Helper III

I assume your Custoemr is a dimension table. If this is a fixed timeframe (31 days), have you considered adding a calculated column to the Customer table, that counts the number of records in the last 31 days, then set a text value to something like "Activity in Last 31 Days" and "No Activity in Last 31 Days". This new column can then be used to filter your customers.

 

Also, when using DatesBetween the first parameter is your date table, not the same table that you are counting from. If you don't have a date dimension in your model, you will want to add one.

 

Hope this helps.

Tegl
Frequent Visitor

Hi

 

thanks for your reply.

 

I like your approach, but not really suitable, as we need a lot of calculations (1 view, 2 views, 3 views etc..)

Tried also by having the First parameter in the Datesbetween from a datetable, but receive same error.

 

any other suggestions?

You could tackle this by first getting a list of customers who have one record ever (which you have inside your COUNTROWS).

Next, get a list of customers who have one record in the last 31 days (again you have the basis for this)

Finally you can use INTERSECT to get the customers in both lists and COUNTROWS of that.

 

So, something like:

Measure =
VAR _alltime = FILTER(...)

VAR _last31days = FILTER(...)

VAR _both = INTERSECT(_alltime, _last31days)

RETURN COUNTROWS(_both)

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors