Look for a value filtering by month

[Juan_91]

Hello, i'm totally new in DAX. Hope you can help me! I don't know what mesure can i do for this example: i have two tables, one of them has 6 different names and the other table has some of the names with the month (like the tables below). I would like to know (making a dynamic table that allows me filter by month) how many times appear the names in, for example, april (in that case only John appears 1 time in April and the others 0 times); or, for example, how many times appear the names in june (in that case John appears 2 times, Tom 1 time, and the others 0 times).

Table 1

Names

John

Michael

Jacob

Oliver

Jake

Tom

Table 2
Date	Names
Apr	John
May	John
May	John
Jun	John
Jun	John
Jun	Tom
Jul	Tom
Jul	Jacob
Aug	Jacob

Actually the real case is another but this example is a representation of what i would need help. 

Thank you so much for the reply 🙂 I really appreciate it. 

[FreemanZ]

hi @Juan_91 

try to plot a matrix visual like:

[Juan_91]

Hi @FreemanZ . Thanks for your reply. Actually i'm working with Power Pivot and Excel. And i would like to do a dynamic table in the excel with the data model. I'm looking for a dynamic table like Table 1 but with one more column containing how many times the names repeat in the month selected. I think i should create a measure with the tables, but i don't know what kind of it. 
Hope i can exlain me well. 

Thanks again. 

[FreemanZ]

hi @Juan_91 

you can just create a standard pivot table with slicer, with no measure, like this:

[Juan_91]

Yes! is that an option. But i have another problem. I have an inactive relationship between the two tables. Is possible to do that but keeping the inactive relationship?

[FreemanZ]

hi @Juan_91 

everything is from Table2, so it has nothing to do or no impact to the relationship with anything.

[Juan_91]

Hi! Ah you do it only with table 2. I would need also table 1 because i would like to know the names that don't appear. If i do an active relationship i can do it, because the dynamic table can show me all the names of table if i want. But in my example i have an inactive relationship 😞 and i don't want to change it. 

[FreemanZ · Accepted solution]

hi @Juan_91 

if you need names column from table1, try like:

measure 1=
CALCULATE(
	COUNTROWS(Table2),
	USERELATIONSHIP(Table1[Names],Table2[Names])
) +0

the point is to add +0 in the end. 

it worked like:

[Juan_91]

Oh yes that works very well in that case! But i had one more problem 😬. Sory😅! I think we are very close.

My table 1 have also dates (like below); and both table 1 and table 2 have an active relationship with a calendar table (that i want to use it for the filter). So, when i do your measure and then filter by date with the calendar table it takes me the dates of table 1 (i don't need that dates)
If i want i can take the dates of table 2 to filter and works very well! But i would like to take the date of the calendar date. 

I promise the next time i will be more clearer. 

Table 1
Date	Names
Jan	John
Jan	Michael
Jan	Jacob
Feb	Oliver
Feb	Jake
Feb	Tom