98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » COUNTIFS referencing two tables
COUNTIFS referencing two tables
Resolved · Urgent Priority · Version 2016
Mon 30 Mar 2020, 17:03Delegate Luca said...
Luca has attended:
Power BI Reporting course
COUNTIFS referencing two tables
Hi
I want to be able to see the company headcount overtime. For this I will need to reference my date table, and my HR data table which includes start and end dates for each employee (if they are still at the company the end date is left blank).
I essentially want to create the following formula in DAX, but am having difficulty.
COUNTIFS('HR_Table[Employee Name], 'HR_Data[Start Date]'<= 'DateTable[Date]', 'HR_Data[End Date]'>='DateTable[Date]')
Any work arounds?
Thanks,
Luca
Tue 31 Mar 2020, 11:02Trainer Jens said...
RE: COUNTIFS referencing two tables
Thank you for the forum question.
I have a suggestion you should try, but it is difficult to know if it is working in your data model.
CountEmp =
CALCULATE(COUNTA(HR_Table[Employee Name]),
FILTER( ALL('HR_Data[Start Date]'), 'HR_Data[Start Date]'<= 'DateTable[Date]'), FILTER( ALL('HR_Data[End Date]'), 'HR_Data[End Date]'<= 'DateTable[Date]'))
Again it is the relationships which filter the tables, so I will not know if this is working in your model. But you will have to use a Calculate function and a Count function (counta or CountRows). You will have to setup the filters in the Calculate function to get the correct result.
You may need to do it a calculated column and use the RelatedTable function to filter it on the second table
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 31 Mar 2020, 18:56Delegate Luca said...
RE: COUNTIFS referencing two tables
Hi Jens
Thanks for replying.
The first formula wouldn't work, and I get the following error when attempting to create a new column in my HR table using the RelatedTable function:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
The relationship between my HR data and the Date Table is the Employee End Date. I have also tried the formula using a relationship with both the Start and End dates, but this has not worked either.
Slightly separate note - what does it mean when the relationship line is dotted?
Thanks,
Luca
Tue 31 Mar 2020, 19:42Trainer Jens said...
RE: COUNTIFS referencing two tables
Hi Luca,
If a relationship is dotted then it is an inactive relation. The data model will be filtered only on active relationships.
You cannot have two relationships from the date table going to the HR table. Then one of them will be a inactive relationship.
I cannot understand why the first option using the calculate function doesn't work.
Which error did the functions return?
Can I ask you to take a screen shot of the the data model and send it to jens.bonde@stl-training.co.uk
I will need to see the model to understand how to do the DAX.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Query Editor ShortcutsCtrl + E: Open or close the Query Editor. |
