DAX Code: count values across two tables

[HenryJS]

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 Actions'[CandidateRef]),'Export Actions'[ActionName] in {"Call - Check In","Call - Follow Up", "Call - Proactive Approach", "Call - Update"},'Export Actions'[ActionDate] )

1. Export Actions

2. Export Actions - History

[Anonymous · Accepted solution]

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

[SteveCampbell]

You can Union the tables in memory in the DAX. Consider using SELECTCOLUMNS if it runs slow.

Candidate Calls = 
var _uniontable = union(TableA, TableB)
RETURN
calculate(DISTINCTCOUNT(_uniontable ,'Export Actions'[ActionName] in {"Call - Check In","Call - Follow Up", "Call - Proactive Approach", "Call - Update"},'Export Actions'[ActionDate] )

Appreciate your Kudos
Connect with me!

Stay up to date on  
Read my blogs on