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 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

 

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

2 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    6 years ago

    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  

  • 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