Count Across Multiple Tables, Date Filter Across Multiple Tables, Measures

[HenryJS]

Hi all,

Please could someone advise the DAX code required to create the below measures?

Some of the measures need to count data across multiple tables. I am also looking to filter the entire page's data by date but there are mutiple date columns across the 3 different tables - please see below.

Column 1: Team

Column 2: Subset

Column 3: Username

Column 4:

• Candidate Calls = Count of 'CandidateRef' where 'Action Name' = "Call - Check In","Call - Follow Up", "Call - Proactive Approach", "Call - Update" in tables 'Export Actions' and 'Export Actions - History'

Column 5:

• Client Calls = Count of 'ContactRef' where "Action Name" = "Call - Check In","Call - Follow Up", "Call - Canvass Call", "Call - Update" in tables 'Export Actions' and 'Export Actions - History'

Column 6:

• New Jobs = Count of 'ActionRef' where "ActionName" = "New Job" in tables 'Export Actions' and 'Export Actions - History'

Column 7:

• Meetings = Count of 'ActionRef' where "Action Name" = "Meeting - Account Review Meeting","Meeting - Contract Review", "Meeting - General Update", "Meeting - Introduction", "Meeting - New Requirements" in tables 'Export Actions' and 'Export Actions - History'

Column 8:

• CV's Sent = Count of 'ActionRef' where "ActionName" = "CV Sent" in tables 'Export Actions' and 'Export Actions - History'

Column 9:

• Employer Interviews = Count of 'ActionRef' where "ActionName" = "Arranged Employer Interview - 1st Interview", "Arranged Employer Interview - 2nd Interview", "Arranged Employer Interview - Skype Session", "Arranged Employer Interview - Telephone Interview" in tables 'Export Actions' and 'Export Actions - History'

Column 10:

• Candidates To Market = Count of 'ActionRef' where "ActionName" = "CV Sent", "CV Sent on Spec" in tables 'Export Actions' and 'Export Actions - History'

Column 11:

• Placements = Count of 'JobRef' where 'JobType' = 'Placed' in table 'Export Jobs'
• How would this work in a table if they look up to different tables?

Date Filter/Slicer:

I want to be able to have a date slicer at the top which filters on dates 'Action Date' in 'Export Actions' and 'Export Actions - History' AND ALSO 'Created Date' in 'Export Jobs'.

1. Example of report visualisation 

2. Export Actions

3. Export Actions - History

4. Export Jobs

[Anonymous · Accepted solution]

I think the first thing you need to do is create a calendar table and relate each of your dates in your other tables to that.  That will allow you filter on a singular date field.

With regard to your measures, you should be able to achieve this with the count rows / filter functions:

https://docs.microsoft.com/en-us/dax/countrows-function-dax

https://docs.microsoft.com/en-us/dax/filter-function-dax

https://docs.microsoft.com/en-us/dax/dax-operator-reference

This will give you something along the lines of:

Measure = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[ColumnName] ="abc" || Table1[ColumnName] = "xyz"))

I've never used countrows across multiple tables but if you want to keep it simple you could just create a measure per table then just add them together in a separate measure.