Forum Discussion

HenryJS's avatar
HenryJS
Post Prodigy
6 years ago
Solved

DAX Code: count values across two tables

Hi all,   I have two tables which have the same columns.   How can I write the below code to return values and look up against both tables?   Candidate Calls = calculate(DISTINCTCOUNT('Export ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI HenryJS ,

    You can try to use the following measure formula to get distinct count from the merged table:

     

    Candidate Calls =
VAR _list =
    FILTER (
        UNION (
            SELECTCOLUMNS (
                'Export Actions',
                "UserName", [UserName],
                "ActionName", [ActionName],
                "ActionDate", [ActionDate],
                "CandidateRef", [CandidateRef]
            ),
            SELECTCOLUMNS (
                'Export Actions - History',
                "UserName", [UserName],
                "ActionName", [ActionName],
                "ActionDate", [ActionDate],
                "CandidateRef", [CandidateRef]
            )
        ),
        [ActionName]
            IN {
            "Call - Check In",
            "Call - Follow Up",
            "Call - Proactive Approach",
            "Call - Update"
        }
    )
RETURN
    COUNTROWS ( SUMMARIZE ( _list, [CandidateRef] ) )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI HenryJS ,

You can try to use the following measure formula to get distinct count from the merged table:

 

Candidate Calls =
VAR _list =
    FILTER (
        UNION (
            SELECTCOLUMNS (
                'Export Actions',
                "UserName", [UserName],
                "ActionName", [ActionName],
                "ActionDate", [ActionDate],
                "CandidateRef", [CandidateRef]
            ),
            SELECTCOLUMNS (
                'Export Actions - History',
                "UserName", [UserName],
                "ActionName", [ActionName],
                "ActionDate", [ActionDate],
                "CandidateRef", [CandidateRef]
            )
        ),
        [ActionName]
            IN {
            "Call - Check In",
            "Call - Follow Up",
            "Call - Proactive Approach",
            "Call - Update"
        }
    )
RETURN
    COUNTROWS ( SUMMARIZE ( _list, [CandidateRef] ) )

 

Regards,

Xiaoxin Sheng