Forum Discussion

Kumkrong_K's avatar
Kumkrong_K
Frequent Visitor
1 year ago
Solved

Counting distinct unique key from seperate tables using DAX

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...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    Try 

    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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

Try 

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
Kumkrong_K's avatar
Kumkrong_K
Frequent Visitor
1 year ago

I never thought of using calculate table to transform them into same headers then union.
I still need to get UserId into AppLog table somehow to test this, but your answer already give me the general idea and direction, which I can pretty much see that it will definitely work.
This look much cleaner than my approach too!

Thank you very much for your assistance.