Count records in related table with expression and test

[jalexander]

I'm trying to get a calculation of the number of IDs that appear on more than 4 days per month (≥ 25% of work days, assuming 20 work days per month) in the following.

I have two tables: Schedule and cardReader. The badge-# is linked in my model, and I've charted MONTHLY the # of IDs on the schedule (a clustered bar or line-over-bar with). So I show 3 IDs allotted for the month of Jan.

I made a measure:   Onsite = DISTINCTCOUNT(swipe_data[badge#])  -- This gives me the number of IDs who've been onsite in that month.

But I'd really like to do  the number of IDs who badged in ≥25% of the work days. So the proper answer should be "1".

(Note, this is simplified -- there are a lot of other columns, but PowerBI seems to already be doing the right thing with the other data.)

Schedule

ID	Date
555	Jan-2020
666	Jan-2020
777	Jan-2020

cardReader

ID	Time
555	1/1/2020  8:00:00 AM
555	1/1/2020  10:00:00 AM
555	1/2/2020  8:00:00 AM
555	1/3/2020  8:00:00 AM
666	1/1/2020  8:00:00 AM
777	1/9/2020  8:00:00 AM
555	1/4/2020  8:00:00 AM
555	1/5/2020  8:00:00 AM

I came up with some non DAX logic, but I'm very new at PBI and I don't know how to loop with these filters and expressions.I've tried COUNTROWS, SUMX, COUNTX, DISTINCT, SELECTCOLUMNS, etc.

The swipe table is large and has 500K rows.

onSite = 
VAR allIDs = DISTINCT(cardReader[ID])
For ID in allIDs do
  numDays = COUNT(FILTER(DISTINCT(DAY(cardReader[Time]), FILTER(cardReader[ID] = ID), cardReader[ID])
  if numDays -gt 4 then
    onSite++
  fi
Done
Return onSite

Thank you

[AlB · Accepted solution]

@jalexander 

1. Create a  date table DateT that has a column Year-Month and create a relationship with card reader 

2. Create an additiional table to use as slicer and select the number of appearances

3. Create this measure and place it in a table visual with DateT[Year-Month]

Measure = 
VAR numAppearances_ = SELECTEDVALUE(SlicerT[Number appearances])
RETURN
COUNTROWS (
    FILTER (
        DISTINCT (cardReader[ID] ),
        CALCULATE ( COUNT ( cardReader[ID] ) ) >= numAppearances_ 
    )
) + 0

Note that, in essence, it is what I already suggested earlier

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@jalexander 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jalexander]

I changed the Measure (which I'm calling "Onsite") to the following, and it now seems to be honoring unique/distinct values for both ID and day-of-month. Yay!

Thanks everyone! (mostly @AlB !)

Onsite = 
VAR numAppearances_ = SELECTEDVALUE(SlicerT[Number appearances])
RETURN
COUNTROWS (
    FILTER (
        DISTINCT (cardReader[ID] ),
        CALCULATE ( COUNTROWS ( SUMMARIZE ( cardReader, cardReader[ID], cardReader[Date] ) ) ) >= numAppearances_ 
    )
) + 0

[AlB · Accepted solution]

@jalexander 

To fix the totals use this measure that uses the one we already had:

Measure TOT = SUMX(DISTINCT(DateT[Year-Month]), [Measure])

The dates have been changed beacause I copied the contents of your table and pasted it in PQ to make some changes and I have a European locale. If you change the locale for import in Options->Current File--> Regional Settings to English (UK) the dates will be as you had it

The table you show shows a blank. That probably means that there are dates in your fact table (or Year-Month) that do not appear in the date table. Check that out. If you can share an abridged version of your pbix by PM (that reproduces the issue) I'll try to have a look.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jalexander]

Wow -- thank you @AlB

After 4 hours I realized that my cardReader Date column was type Date/Time instead of Date, and my ID as 123 instead of text. After correcting those it's working great.

Note-- for some reason in your reworked demo pbix file the cardReader Time and Date values got their day and month transposed. So where I had 1/4/2020 you had 4/1/2020. I also noticed that the table totals are still a bit wierd.

I set Num-appearances to 2 (since your data has Jan thru May with only 1st and 2nd day of the month, due to the transposition) ... and here's your table,,, Why is 2+2+2+3+1 = 5 ? I think the answer is that it's the total of unique IDs that appear in the Measure counts, throught the months in the column.

Anyway, great solution, and this was real learning experience for me. Thanks again for your help, AIB. You're an inspiration!

[AlB · Accepted solution]

@jalexander 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@jalexander 

1. Create a  date table DateT that has a column Year-Month and create a relationship with card reader 

2. Create an additiional table to use as slicer and select the number of appearances

3. Create this measure and place it in a table visual with DateT[Year-Month]

Measure = 
VAR numAppearances_ = SELECTEDVALUE(SlicerT[Number appearances])
RETURN
COUNTROWS (
    FILTER (
        DISTINCT (cardReader[ID] ),
        CALCULATE ( COUNT ( cardReader[ID] ) ) >= numAppearances_ 
    )
) + 0

Note that, in essence, it is what I already suggested earlier

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[jalexander]

Thanks for all your responses, but I'm still unclear.

@AlB  Did you mean I should upload a demo pbix file. I created one but I don't see how to attach it here, so I've linked to it: 

http://etrafficcontrol.com/sites/default/files/Onsite.pbix

• Data depicts allocated Staff, and measured traffic via badge card reader.
• Desire is to:
    -- Show how many Staff came in, say 4 days or more each month. Would be great if "4" value could be a slicer rather than hard-coded.
    -- ignore secondary entrances on the same day -- just want to measure once per day.
• Five Employees IDs are 1111, 2222, 3333, 4444, 5555.
• Data cardReader traffic:
    -- Employee 1111 came in 3 times on Jan-1, came in 5 days in Jan; 5 days in Feb.
    -- Number of days each Employee (1111-5555) came in during Jan is 5,3,2,2,1 ; in Feb is 5,5,4,1,0 respectively.
• Staff column shows the total full-time staff-unit allocated; two x 1/2-time IDs equals 1.
• I've created a few Measures, each using the various suggestions from you folks.
    -- Employees_simple just counts the number of unique IDs in the cardReader table.
    -- What I need is a way to count the number of UNIQUE IDs, AND for each UNIQUE ID, the number UNIQUE DAYS, AND within the time frame that each point of the charts is showing (e.g. Months), whether it's month, quarter, or year say if I use the drill down/up feature.
    -- Empoyees_A/B/C are attempts to use distinct day, but stumped on keeping it in the timeframe of the chart datapoints.

Again, the struggle I'm having is that I want to keep the querry of the cardReader data to the time-frame (e.g. month) that's being used ("filtered"?) for the bar showing allocated staff, AND count unique daily in that timeframe (vs all; I don't want to count multiple entries within a day)

• The correct behaviors would be show "1" for Jan, and "3" for Feb in Employees column. (using >=4) -- Meaning that in jan one (1) employee came in four (4) or more days; in Feb three (3) employees came in four (4) or more days.
• Why, when Filter for Month is set to all, does the table and bar chart show Jan-Dec/12 months? Why not just the two months in the data: Jan + Feb?
• Why is the total in the table for Staff = "9", but "3" for Employees? Shouldn't it be "6"? (This is despite that this column isn't doing what I want it to -- with the correct vaules Jan=1 & Feb=3 totals should be "4")
Thank you again

[AlB]

@jalexander 

The code above assumes you have month in the filter context. Yes, you can best create a date table with a relationship tou your fact table(s) and use it in your visuals.

Best if you can a pbix with a dummy model so it is easier to understand what you are after.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@jalexander , I am not clear on the relation of the card reader with the badge. Also, is 20 a static number?

You can get formula like

countx(filter(Summarize(cardReader, cardReader[ID], "_1" ,count(cardReader[Time])/20), [_1] >.25),[ID])

[jalexander]

Although I originally thought PBI was limiting the data being returned from cardReader table to the same date range each bar/line/point in my chart of the primary table, Schedule,... it's not -- rather it's returning results for the whole table, ignoring the dates. So for example, both tables cover Jan-Dec 2020, so for say Jan-2020, I get the number of Staff (# of IDs) from table "Schedule" for that month, but I get the ALL of the records from cardReader -- the whole year, not just the ones that fall in the same date range. I don't want to hard code this, becasue I want to enable drill-down (drill-up, to Quarter and Year). How can I tell the query in DAX to limit to the same date range? Do I have to use some sort of Date-table?

(Note -- I duplicated the cardReader Time column to Date. thinking that I could 'equate' it easier than a time. So confused... cardReader[Date] (no specific format is set) should match the month in Schedule[Date] (which is in MMM yyyy)

Thank you again

[AlB]

Hi @jalexander 

Try this:

Measure =
COUNTROWS (
    FILTER (
        DISTINCT ( swipe_data[badge#] ),
        CALCULATE ( COUNT ( swipe_data[badge#] ) ) >= 4
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers