Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
My first post on your eminently useful forum. I’ve been using PowerBI Desktop for a few weeks now, so I’m a newbie but have been reading a lot on here!
Background
I’ve a transaction table with customers (AccountID) who purchase something at a site (entry in tblTransaction).
I’ve a complicated overall table structure, but this issue centres around this main Transaction table.
tblTransaction has TimeStamp (withtime), so I’ve created TimestampDate (no time) to enable a relationship to a date table (customtblActiveMembers) where I’ve successfully added a calculated column against each day. It creates an overall distinct count of “active customers” (tblTransation.AccountID) by day (the definition of an “active” customer is one who has bought something in the last 3 months at a site) using this :
Active Members In Last 3 Months = /* calculated column counting the unique people with transactions in the last 3 months based on formula from: https://sqldusty.com/2015/10/21/10-dax-calculations-for-your-tabular-or-power-pivot-model-part-2/ */ CALCULATE ( DISTINCTCOUNT ( tblTransaction[AccountID] ), DATESBETWEEN ( customtblActiveMembers[Date], DATEADD ( customtblActiveMembers[Date], -3, MONTH ), customtblActiveMembers[Date] ), tblTransaction[AccountID] <> BLANK () )
However, my issue is that I can’t work out the right approach to add tblTransaction.SiteID into the data - so that I have “active” customers by site by day. What is the best approach?
So far I’ve tried:
Thanks,
Simon
Solved! Go to Solution.
Solved.
Thanks @v-yulgu-msft for your advice. The measure you made wouldn't work but your best advice was to read the link on how to best ask a question. Whilst preparing a dedicated pbix file, i realised an important lesson: play with a smaller dataset to understand what's going on.
Turns out (as I've been reading) that measures are generally the best thing to use in most cases.
My measure which solves my issue (i.e. distinct count of Account numbers for people with a transaction in the last 3 months is this):
Active in last 3 months = /* Measure. Put in tblTransaction. Key: tblDate is a date table, linked by date to tblTransaction */ CALCULATE ( DISTINCTCOUNT ( tblTransaction[AccountID] ), FILTER ( ALL ( tblDate[Date] ), tblDate[Date] <= MAX ( tblDate[Date] ) && tblDate[Date]>= MAX ( tblDate[Date] ) - 90 ) )
and for those reading who might benefit, here are my own personal notes on how I understand this to work:
Explanation (took me 4 days to make/understand this measure):
Setup:
CALENDAR(DATEVALUE("2011-01-01"), TODAY())
Measure:
My .pbix file has been uploaded here if anyone wants to see: https://files.fm/u/paeeqqkx
Hi @MrSimonC,
If you want to count the “active customers” per site, please try to use an ALLEXCEP function in above formula:
Active Members In Last 3 Months =
CALCULATE (
DISTINCTCOUNT ( tblTransaction[AccountID] ),
FILTER (
ALLEXCEPT ( tblTransaction, tblTransaction[SiteID] ),
DATESBETWEEN (
customtblActiveMembers[Date],
DATEADD ( customtblActiveMembers[Date], -3, MONTH ),
customtblActiveMembers[Date]
)
&& tblTransaction[AccountID] <> BLANK ()
)
)
Besides, please provide some simplified sample data and show us your desired result so that I can replicate and test your scenario in my environment. How to Get Your Question Answered Quickly
Regards,
Yuliana Gu
Solved.
Thanks @v-yulgu-msft for your advice. The measure you made wouldn't work but your best advice was to read the link on how to best ask a question. Whilst preparing a dedicated pbix file, i realised an important lesson: play with a smaller dataset to understand what's going on.
Turns out (as I've been reading) that measures are generally the best thing to use in most cases.
My measure which solves my issue (i.e. distinct count of Account numbers for people with a transaction in the last 3 months is this):
Active in last 3 months = /* Measure. Put in tblTransaction. Key: tblDate is a date table, linked by date to tblTransaction */ CALCULATE ( DISTINCTCOUNT ( tblTransaction[AccountID] ), FILTER ( ALL ( tblDate[Date] ), tblDate[Date] <= MAX ( tblDate[Date] ) && tblDate[Date]>= MAX ( tblDate[Date] ) - 90 ) )
and for those reading who might benefit, here are my own personal notes on how I understand this to work:
Explanation (took me 4 days to make/understand this measure):
Setup:
CALENDAR(DATEVALUE("2011-01-01"), TODAY())
Measure:
My .pbix file has been uploaded here if anyone wants to see: https://files.fm/u/paeeqqkx
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |