Report: DAX Code, Date Filtering Across Multiple Tables

[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. Table: 'Export Actions'

3. Table: 'Export Actions - History'

4. Table: 'Export - Jobs'

Thanks,

Henry

[AnthonyTilley]

In order to filter all of the tables by a given date create a calander table and link all of your tables to this Table 

To add calander table go to Modeling and select new table 

in the formula bar add the following code (change the start and end date to the required dates needed

Table = CALENDAR(01/01/2019,31/12/2020)

For your individual measures i would create a headers table 

Go to Home and in the data section click Enter data. in hear add a table similar to below

Header Order	Header
1	Candidate Calls
2	Client Calls
3	New Jobs
4	Meetings

You will use this table for your colunm headers 

Next make a measure in your newly created headers table for MaxHeader = Max(Headers[Header Order])

This will used later to combine all of your measures into one measure 

Next create each of your individual measures 

for example 

Client Calls = calculate(Count(ContactRef), Action Name = "Call - Check In" || Action Name ="Call - Follow Up" || Action Name ="Call - Canvass Call" || Action Name ="Call - Update")

you need to make a different measure for each of your headers it doesnt matter if they come from diffrent tables.

Finally make your final Measure to combine all of your individual measures using a switch statment

in this function the first entry is your criteria in this case maxHeader, followed by the value and then what measure to use

so if the max header is 1 the candedet calls measure will be returned.

Example

Final measure = Switch(MaxHeader,
                  1,Candedet Calls,
                  2,Client Calls,
                  3,Jobs Calls,
                  4,Meetings Calls,
"No Measure Found")

sorry for the rushed explinations 

Let me know if you have any issues and i can tr to assist 

[HenryJS]

Hi @AnthonyTilley 

I have created a new report. Added all the data sources. Create a Calendar Table and linked to all date columns in the modelling tab as per below.

 

When I created the measures as per below it did not work and it also doesn't look across multiple tables?

Client Calls = calculate(Count(ContactRef), Action Name = "Call - Check In" || Action Name ="Call - Follow Up" || Action Name ="Call - Canvass Call" || Action Name ="Call - Update")

I need measures which look across both the 'Export Actions' and 'Export Actions - History' tables.

Thanks,

Henry

[AnthonyTilley]

Client Calls = 'Export Actions' and 'Export Actions - History' tables.
Var EA = calculate(Count('Export Actions'[ContactRef]), Action Name = "Call - Check In" || Action Name ="Call - Follow Up" || Action Name ="Call - Canvass Call" || Action Name ="Call - Update")

VAR EAH = calculate(Count('Export Actions - History'[ContactRef]), Action Name = "Call - Check In" || Action Name ="Call - Follow Up" || Action Name ="Call - Canvass Call" || Action Name ="Call - Update")

RETURN EA + EAH

When a single Measure needs to calculate from two tables use a Veriable to hold the value from each table and then add the two together when returned 

in this example the first veriable EA will calculate the count of calls from the Export Actions table that meet the criteria 

The second Veriable EAH will Calculate the same thing but from the Export actions - history table 

the final statment returns the Value by adding the two veriables together

The example above is not compete you will need to put the correct table name before each colunm to make it work 

[HenryJS]

Hi @AnthonyTilley 

Var doesn't exist. It allows either VAR.p or VAR.s and it makes me pick a table?

[AnthonyTilley]

You do not have a return clause 

var is a veriable and is only holding your information 

To return the Var you need to include a Reurn function 

After the end of your veriable add a line for 

"Return EA"

Also your calculate function is incomplete at the end you state [Action Date]))) but you do not have a condition for example [action date] = date(2019,1,1)

if you intend to use the date a s filter or in your matrix then you do not need to include this in your calculate function. 

your calculate function should end after your in clause

If You are able to create a a static version of your your PBIX file (include enough data for examples) and share it then i can take a look and write some examples in your file for you to review.

[HenryJS]

Hi @AnthonyTilley 

How can I send you the .pbix file?

Much appreciated.

Thanks,

Henry

[AnthonyTilley]

Save it in a onedrive, dropbox or google drive and then create a shared link and post the link here. 

ensure you set the others are able to access and download the file. 

Make sure that the file does not contain links to data files. add the data as a static table saved in the pbix file

[HenryJS]

Hi @AnthonyTilley 

Please see below link

Let me know if it works

Many thanks

https://drive.google.com/file/d/1bM0RsRo9SWKn8i-nA_PahAzCiKqCC5nP/view?usp=sharing

Thanks,

Henry

[AnthonyTilley]

i made some changes that should help

the date table was not formated correctly so i changed this to pull dates from 2019 to 2020

your dates in your tables where date time so would not join correctly 

i changed these to a new colunm called Report date to show you the diffrence 

i then created your measures in a fact table to show how you can calculate from 2 diffrent tables 

in order to split by team and user you will need a user table with these details that you can join to the three tables on the user id 

PBIX file