Use DAX to count any instance of name in one table that occur in a certain year in four related tables.

[WilliamPHII]

Hi,

I'm new to Mr. Excel, (and to DAX). My problem is that I have a name table with only a list of names. These names appear in four other tables which include dates (There are multiple occurrences of the names, and multiple dates. Not all names appear in all tables).

I'd like to use DAX to filter out all names which occur in at least one of the tables with a date in 2018 (one of the years available from the total date options). In other words, if "Bob" shows up in several of the tables but doesn't have a date in 2018 showing, he would not be in this group.

I really just need a count function that counts the instances of the names in the other four tables for 2018. Then I can run a pivot and filter out any names with 1> count.

Any help would be great!

Thanks.

William

 

[macfuller]

I suggest a helper column (or 4, one for each table you're searching if the code gets too complex or takes too long to run). You don't say if the tables are related, so let's assume they're not.

A helper column in the name table for the info in the date tables might be:

Name in DateTable1 =IF (
    CALCULATE (
        COUNTROWS ( DateTable1 ),
        FILTER ( DateTable1, Names[Name] = DateTable1[Name] && YEAR ( DateTable1[Date] ) = 2018 )
    )
        > 0,
    "Exclude",
    "Include"
)

Then you can filter on whether to include or exclude the name.

Row filtering always trips me up... you may need to do EARLIER(Names[Name]) in the above code if DAX says it can't resolve the field value.

 

[WilliamPHII]

I appreciate the thoughtful answer. Yes, the tables are related. You make an excellent point about using a helper column. I will look into that sometime soon here when I'm not supposed to be on the clock...

Thanks again for taking the time to reply.

William