Forum Discussion

MrSimonC's avatar
MrSimonC
Frequent Visitor
8 years ago
Solved

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

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 ...
  • MrSimonC's avatar
    MrSimonC
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
MrSimonC
Frequent Visitor
8 years ago

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