Adding a second dimension to an active linked table with a count and date range

[MrSimonC]

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:

• (probably the best approach) Creating another table with Day by Site (e.g. "1/1/17" "Site 1", "1/1/17" "Site 2" etc.) and add a adding a calculated column against each row. However we all know, powerbi won’t link on 2 active connections to the same table (i.e. Date & Site), so how do I link it? Maybe with 2 tables:
• Tried linking the above mentioned table via 2 separate, unique valued tables – one for date, one for site. For this to work I would need bi directional filtering turned on – but powerbi takes so long to do this, I have to end-task and restart it
• I’m not fluent in DAX enough to create a measure for this … but then wouldn’t the first approach I mentioned with another calculated column be better, else how can site be filtered on (eventually) in a chart?
• Tried creating a combined site+date column in both above tables, but this will only link directly on each row appropriately rather than look back 3 months...

Thanks,

Simon

[v-yulgu-msft]

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

[MrSimonC · Accepted 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:

• tblDate = date table contains all dates from 2011 to now (via code)

  CALENDAR(DATEVALUE("2011-01-01"), TODAY())

• tblTransaction = where the measure is created, linked by date (tblTransaction *-1 tblDate)

Measure:

1. Before we start, always keep in mind that tblDate will filter tblTransaction (due to it's 1:* relationship as direction travels 1:* automatically)
2. Starting with FILTER, escape any Evaluation contexts in the graph/slicer with ALL() to work on the whole dates table
3. Restrict tblDate to dates in the past (in a measure, the current row evaluation's date is accessed by MAX(tblDate[date]))
4. Also restrict tblDate to dates in the past but are newer than 90 days ago
5. Due to the relationship on date, tblTransaction is now filtered programatically by on the last 90 days by the above steps and the user can further filter by graph on studio or a slicer graphically
6. Finally calculate the DISTINCTCOUNT of just AccountIDs

My .pbix file has been uploaded here if anyone wants to see: https://files.fm/u/paeeqqkx