Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@Anonymous 

 

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 @Anonymous 

 

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!!

 

Anonymous
Not applicable

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 @Anonymous 

 

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!!

 

Anonymous
Not applicable

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 🙂

 

 

@Anonymous 

 

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!!

 

Anonymous
Not applicable

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.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors