Count cross referenced columns between two tables

[jalexander]

I'm trying to create a report that shows how many people, each assigned to a variety of programs, appeared onsite per month, on at least N number of days. I've managed to get the number of IDs assigned to the program to show per month, but having difficulty getting a count of the number of matches per time-period (e.g. month) of unique IDs and on unique days.

So in this example, I have seven user IDs: 1111,2222,...,7777.  I have two programs: AA,BB. I have five months of data January-May: Schedule, which shows who is assigned to each program, AA & BB, for each month. (and what fraction of their time -- tho I'm not using this data yet.) The cardReader table shows who appeared onsite; and I'm disregarding multiple appearences per day.

I also have a slicer that lets me set the threshold of how many days when counting the people who appeared, and I'll add another slicer to filter the program numbers. (But I could just set some number for the sake of making the logic work, like ">=3" .)

The Report, mainly displayed as monthly, needs to show "#Sched." how many people are assigned to the program (AA in this case); and "Onsite" the number of people that appeared--who are assigned to that program--on at least N number of days (the SlicerT selection). The report table below shows what number should show in the Onsite column for each of the Slicer selections 1-6 days.

How can I properly FILTER and CALCULATE ( COUNTROWS ( SUMMARIZE ))) to get measure to work in this way? I've tried various suggestions, and have a few nonworking measures. I also have a Date table to help get the time-period to link up.

Both the Date and ID are linked between these tables.

Why doesn't this work? Shouldn't [whatever drives the visual data] ALSO honor the Pogram column in the Schedule table, while this code counts the number of ID matches where the ID and Date combo are unique from the cardReader table?

(This is updated -- the solution I used)

NumVisits = 
COUNTROWS ( SUMMARIZE ( FILTER(cardReader, cardReader[ID] = Schedule[ID] && MONTH(cardReader[Date]) = MONTH(Schedule[Date])), cardReader[ID], cardReader[Date] ) ) + 0

Onsite = 
VAR numAppearances_ = SELECTEDVALUE(SlicerT[Days onsite])
RETURN
COUNTROWS (
    FILTER ( Schedule, Schedule[NumVisits] >= numAppearances_ )
) + 0

Thank you.

Edit -- Here's the updated data with the solution's NumVisits column

[littlemojopuppy]

Glad I could help a little bit 🙂

[jalexander · Accepted solution]

Hi All,

Here's a solution -- this is inspired by @littlemojopuppy, who pointed out that a new column is needed that totals the attendance counts of each program assignee. Since between the two tables, the Schedule table is more structured than the cardReader data, and it's time scale is monthly, adding a column, NumVisits, to Schedule with the monthly count of onsite appearances by each ID allows an easy expression for the needed measure, Onsite.

NumVisits = 
COUNTROWS ( SUMMARIZE ( FILTER(cardReader, cardReader[ID] = Schedule[ID] && MONTH(cardReader[Date]) = MONTH(Schedule[Date])), cardReader[ID], cardReader[Date] ) ) + 0

Onsite = 
VAR numAppearances_ = SELECTEDVALUE(SlicerT[Days onsite])
RETURN
COUNTROWS (
    FILTER ( Schedule, Schedule[NumVisits] >= numAppearances_ )
) + 0

 It should be noted that the Total for #Sched. in the table is the total number of individuals (IDs), not the total person-days assigned, as that's performed by another data column.

Thank you all for your help and inspiration!

[Anonymous]

Hi @jalexander ,

Based on your description, I'm a little confused. I did a test. Is the following result what you want?

Hope that's what you were looking for.

Best Regards,

Yuna

[jalexander]

I'm not sure what your SlicerT value is.

I think I need to put the program filter into my DAX expression. I'm confused about the relationship between filters PowerBI is using outside the Measure definition vs what's being filtered inside. For example, PBI is filtering on Date, so that a graph with date segments (day, month, year) has corresponding data from the tables. Also, the program data. With a filter (on visual/page/report) program=AA -- I can say

#Sched. = DISTINCTCOUNT(Schedule[ID])

and I get "3" --> three distinct IDs with program=AA in January.

But when I need to get only those IDs with the program=AA in Schedule -- E.g. below the DISTINCT(cardReader[ID]) needs to be changed to something like DISTINCT(Schedule[ID] where program=$program && date=$time-increment)  -- having $program="AA" when that visual filter is in effect, and $time-increment= time slice used for the graph/chart point.

COUNTROWS (
    FILTER (
        DISTINCT (cardReader[ID] ),
        CALCULATE ( COUNTROWS ( SUMMARIZE ( cardReader, cardReader[ID], cardReader[Date] ) ) ) >= numAppearances_ 
    )
) + 0

I feel like I missing a major concept here. So is there some way to pass variables like this into DAX expressions? Or do I have to write convoluted syntax that manages to make that [effectivly] happen.

Here's a tanble of what Onsite values should be, For January-May, with SlicerT set to values one-six. (That's with the data I originally posted, 16 Schedule rows and 46 cardReader rows.)

(above table corrected from ealier)

[littlemojopuppy]

Haven't incorporated the slicer value yet...trying to get basic functionality first.

You can add in the Program filter into the expression, but the problem is that there's nothing in CardReader that says what they were going to...Program AA, BB or maybe they just checked in because they had no where better to go.  That's going to be the hard part.  If there were more data such as what they were checking into would be helpful.  If not, we (you) make assumptions that no one was on site unless they were scheduled to be, that would help.

You can create variables in the logic, pull in values that are part of the current filter context, pass in "what if parameters" which are kind of like your SlicerT.  Not sure what other kind of variables you're thinking.

[littlemojopuppy]

Looking better?

The measure is below

Attendance Day Count = 
    VAR AttendanceDates =
        ADDCOLUMNS(
            DateT,
            "Attended",
            [Number Onsite]
        )
    RETURN

    COUNTROWS(
        FILTER(
            AttendanceDates,
            ISBLANK([Attended]) = FALSE()
        )
    )

One problem is going to be determining attendance at program AA vs BB instead of just being on site because there's nothing in the CardReader to indicate what they were attending.  But I'm sure I can figure this out 🙂

[littlemojopuppy]

Ok...so I'm encouraged that when looking at my results by program, it's very very close to what you're showing for scheduled for Program AA.

And if I'm understanding you correctly, you need to have the number of dates that each ID was onsite in a given month.  So if Bob was on site Dec 12-15 that would be four and Mary Dec 17-19 that would be three.  Correct?

[jalexander]

Right -- if Bob and Mary are both assigned (any % of thier time) to program AA in December, and Bob is onsite Dec 12-15 (four distinct days) and Mary Dec 17-19 (three distinct days), then with SlicerT set to 3-days (3 or more days), then the Onsite value should be 2 (two different/unique people showed up >=3 days); And with SlicerT set to 4 (>=4 days), then Onsite = 1 (only one person, Mary, showed up 4 or more days). And this is just for the AA program. Data in Schedule exists for multiple programs, and the cardReader data is only specific to user IDs.

[littlemojopuppy]

I'm a little confused on what you're trying to accomplish...but I took a shot anyway.

First thing is you need to mark your date table as being a date table (right click on the table in the fields pane and Mark as Date Table).

Second thing...this will probably be easier if you change your data model to look like the following

Here's some output that I came up with...does this look right or even close to correct?

The measures I used are really basic...

Number Scheduled = DISTINCTCOUNT(Schedule[ID])

Number Onsite = DISTINCTCOUNT(cardReader[ID])

Happy to help more but need some clarification/simplification of what you're trying to do 🙂

[jalexander]

Ahh -- I didn't know I was supposed to mark the Date table as a "Date Table" 😯

OK, so after marking DateT as a date-table, it is working better... but it's not filtering in such a way that only IDs are matched from cardReader with matching "Program" in Schedule for the corresponding month.

So, it's returning a count = number of IDs matching Date in schedule (and unique day appearences in cardReader); RATHER than desired count = number of IDs matching Date AND Program in Schedule (and unique day appearances in cardReader).

It's a little hard to contrive a single sentance to what the logic should do. I'll try again:

"Onsite" resulting value, for the January row, with the SlicerT set to "4", should be the total number of individual people (unique IDs), who are assigned to program AA in the Schedule for January, that appear in cardReader on at least 4 different days, of that month.

The idea is to measure attendance for a particular program as a percent of the folks assigned to that particular program. So say for Slicer = 4-days -- how many people assigned to program AA appeared onsite on 4 or more days. Does that make more sense?

So it think I need to work in a filter that accounts for the Program column. I need to FILTER on Program as well as DISTINCT on ID.