Counting distinct unique key from seperate tables using DAX

Question

Greeting and thank you for your time coming to read my problem.

I have some problems with my DAX which it is half showing what I want

I have about 4 tables,

DateTable - store dates, end date is TODAY()

Users - user informations and some keys, user_id, firebase_id

AllPoint - transaction point, relate to users by user_id

AppLog - firebase log, relate to users by firebase_id

both the AllPoint and AppLog table also relate to date table by date of their own

user - AppLog is * to * because some not using web, so they have (blank) as their firebase_id,

the rest are unique key

so what I'm trying to do is, to count how many user still participate with me during the last 6 months, aka 180 days,

via transaction, app, or both

I also want it to able to able to backtrack,

with nothing select, it should base on today (November) and calculate from June through November

if I select the previous month (October), it should count from May through October and so forth

my DAX is this

6mthActive =

VAR sd = MAX(DateTable[Date])

VAR ld = sd-180

VAR _result =

COUNTROWS(

FILTER(

ADDCOLUMNS(

SUMMARIZECOLUMNS(

'Users'[user_id],

'Users'[created_date],

"tDate", MAX(AllPoint[TransDate]),

"aLog", MAX(AppLog[Created])

),

"AC", MAX([tDate],[aLog])

),

[AC] >= ld

&& [AC] <= sd

&& [created_date] <= sd

)

RETURN

_result

what I'm doing is create a VAR table to look through the last date such user has transaction or using app, whichever is latest

using the summarizecolumns and addcolumns

then using filter to select only those withing the 6 months, aka 180 days

then countrow, since user_id is unique key

the result turn out fine with nothing select

but when I select month, for example October

it only calculate user that participate with me on October, not from May->October as I intended

I tried distinctcount separately on AllPoint and AppLog,

ActiveTrans =

VAR sd = max(DateTable[Date])

VAR ld = sd - 180

VAR _result =

CALCULATE(

DISTINCTCOUNT(AllPoint[user_id]),

AllPoint[TransDate] <= sd,

AllPoint[TransDate] >= ld

)

RETURN

_result

but then there some dupe, since many user are both using transaction and app

I tried using crossjoin, but couldn't understand it and make no progress

So if anyone could provide me with solution or insight, I'd be really appreciate your thought

Again, thank you for your time.

johnt75 · Accepted Answer

Try&nbsp;
6 month active =
VAR EndDate =
    MAX ( 'Date'[Date] )
VAR StartDate = EndDate - 180
VAR AppLogUsers =
    CALCULATETABLE (
        DISTINCT ( AppLog[UserID] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
VAR AllPointUsers =
    CALCULATETABLE (
        DISTINCT ( AllPoint[UserID] ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
VAR CombinedUsers =
    DISTINCT ( UNION ( AppLogUsers, AllPointUsers ) )
VAR Result =
    COUNTROWS ( CombinedUsers )
RETURN
    Result

Forum Discussion

Counting distinct unique key from seperate tables using DAX

2 Replies

Related content

count distinct

Distinct Count

Distinct count

DAX Calculation for Distinct Count

Average - Distinct Count