Forum Discussion
Post ProdigyCount Across Multiple Tables, Date Filter Across Multiple Tables, Measures
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
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.
1 Reply
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.