topic Re: Look for a value filtering by month in DAX Commands and Tips

Look for a value filtering by month

Juan_91 — Fri, 03 Mar 2023 02:25:38 GMT

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.

Re: Look for a value filtering by month

FreemanZ — Fri, 03 Mar 2023 02:45:45 GMT

hi Juan_91

try to plot a matrix visual like:

Re: Look for a value filtering by month

Juan_91 — Fri, 03 Mar 2023 11:31:00 GMT

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.

Re: Look for a value filtering by month

FreemanZ — Fri, 03 Mar 2023 12:27:36 GMT

hi Juan_91

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

Re: Look for a value filtering by month

Juan_91 — Fri, 03 Mar 2023 12:46:00 GMT

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?

Re: Look for a value filtering by month

FreemanZ — Fri, 03 Mar 2023 13:12:22 GMT

hi Juan_91

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

Re: Look for a value filtering by month

Juan_91 — Fri, 03 Mar 2023 13:18:09 GMT

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.

Re: Look for a value filtering by month

FreemanZ — Fri, 03 Mar 2023 13:35:40 GMT

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:

Re: Look for a value filtering by month

Juan_91 — Fri, 03 Mar 2023 14:30:23 GMT

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