Forum Discussion
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 ...
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
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!