Joining two tables and applying year filter w/o using merge queries

[Anonymous]

Hi, I recently started using POWER BI and I am trying to do a single join as I would do in SQL. 

I have two tables: 

Table A
Year	Customer ID	Account ID
2018	1000	1
2018	2000	2
2018	3000	3
2018	4000	4
2018	1000	8
2018	2000	7
2018	3000	6
2017	4000	5

and 

Table B
Customer ID	Customer Name
1000	A
2000	B
3000	C
4000	D

The table that I want to get is a summary table showing the "Number of Accounts" by "Customer Name" where "thisyear" filter is applied (without explicitly typing 2018). 

Is this something that I can achieve without using merge queries but using a DAX function?

Table C
Customer Name	Number of Accounts
A	2
B	2
C	2
D	1

Thank you for the help!

Kerem

[MFelix · Accepted solution]

Hi @Anonymous,

You should use the relationship between tables to make this and then just add it to a table visual the Name from TableB and the Account from TableA.

For the account select the sumarization COUNT. 

If you add a filter based on year you can then get the expected result.

However if you don't want to have a slicer and just want to filter the count based on the current year without any selection you can add the following measure:

Number of accounts = CALCULATE(COUNT(TableA[Account ID]); TableA[Year] = YEAR(TODAY()))

Then use this measure on your table. Check below the two option on the image:

Check attach PBIX file with example.

Regards.

MFelix

[Anonymous]

Hi @MFelix thank you for your reply. It worked. And thanks for showing the solution in two different ways. I used the measure. 

Very helpful!

Kerem