Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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!!
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!!
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!!
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!!
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..
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.
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)
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |