topic Re: How to show all user ids which were NOT active during a sliced date range in DAX Commands and Tips

How to show all user ids which were NOT active during a sliced date range

WhoCares535454 — Fri, 11 Nov 2022 14:59:33 GMT

Hi there. I have some data, centred around two tables.

Table 1 is a list of all users, complete with distinct user ids, names and other data.

User ID	Name
628346	a
057378	b
202674	c

Table 2 is a list of every time users accessed a system, including their user ID, the date they accessed the system, and other data

User ID	Date
057378	10/11/2022
057378	11/11/2022
202674	11/11/2022

On my visuals, I have a nice table showing a list of all users who accessed the system, and a date slicer (using the date field on table 2) allowing this to be filtered to only show users who accessed the system during certain date ranges. It works fine. What I want is the opposite: another table on the same page, which shows all users who have not accessed the system during the filtered date range. This would include all users who have never accessed the system, plus all those who have accessed it, but not during the filtered date range.

I've tried doing a simple "does not appear on table 2" filter, but that just means that as soon as we start slicing by date, the table goes blank. The closest I've had to success with this is the measure below, which returns the count of user IDs which don't appear. If I apply that as a filter to the table ([Users with no usage] >0), it appears to work, but it takes so long to run every time I change the date slicer that it usually times out. I think it's taking a long time because it's creating the entire table for every user id, deleting it, then doing it again for the next user id.

[Users with no usage] =

calculate(
count(table1[user id]),

except(

allselected(table2[date]),

values(table2[date])
))

I've tried getting the measure to create a table using calculatetable() instead of calculate(count()) but visuals refuse to display it, saying that a table of multiple values was supplied where one value was expected. I'm stuck. Can anyone help?

Re: How to show all user ids which were NOT active during a sliced date range

FreemanZ — Fri, 11 Nov 2022 13:33:26 GMT

Not sure if this is what you expect:

NotSelected =

VAR _Table = EXCEPT( ALL (Data[Name]), VALUES (Data[Name]))

RETURN

CONCATENATEX(

_Table,

Data[Name], ", ", Data[Name], ASC

)

BTW, in your code, COUNT returns a value not a list/table.

Re: How to show all user ids which were NOT active during a sliced date range

WhoCares535454 — Fri, 11 Nov 2022 14:01:10 GMT

Thanks, but that's not really what I want. Displaying the users in a single string wouldn't work, as there are several thousand users in total. What I want is to return a table which displays all users (on separate rows) who have not been active in the set date range according to my slicer.

So for example let's say I have the numbers 1-30 as the different user ids (pic 1), and every day some of them log onto the system (pic 2).

What I want to see is something like pic 3: the slicer lets me select a date range, and then the table next to it displays a list of all user ids who did not log onto the system during this date range. Is that helpful?

I'm aware count returns a value. I've tried getting the measure to return a table with calculatetable instead of count, but whenever I try to display the measure I get told "a table of multiple values was supplied where one value was expected).

Re: How to show all user ids which were NOT active during a sliced date range

zerotyper — Fri, 11 Nov 2022 15:40:11 GMT

WhoCares535454 please try the below code :

Inactive = VAR _mindate = MIN ( 'Table2'[Date] ) VAR _maxdate = MAX ( 'Table2'[Date] ) VAR _activetbl = FILTER ( 'Table2', 'Table2'[Date] >= _mindate && Table2[Date] <= _maxdate ) VAR _actuser = SELECTCOLUMNS ( _activetbl, "@user", [User ID] ) VAR _user = VALUES ( Table1[User ID] ) VAR _inactive = EXCEPT ( _user, _actuser ) RETURN COUNTROWS ( _inactive )

put the measure into the filter of the matrix

Re: How to show all user ids which were NOT active during a sliced date range

WhoCares535454 — Fri, 11 Nov 2022 16:02:13 GMT

Hi, thanks for this. I'm afraid when I try using this code, setting the filter, and then selecting a date range with the slicer, the table goes completely blank, with no results displayed. If no date range is selected with the slicer (i.e. all dates are selected) it seems to work, but as soon as the dates are filtered it goes completely blank. I don't suppose you have any idea what's going wrong?

Re: How to show all user ids which were NOT active during a sliced date range

zerotyper — Fri, 11 Nov 2022 16:12:58 GMT

Do you have a date dimension table?

Re: How to show all user ids which were NOT active during a sliced date range

WhoCares535454 — Fri, 11 Nov 2022 16:20:00 GMT

I do, I haven't used it so far as I've been using the date value in table 2.

Re: How to show all user ids which were NOT active during a sliced date range

zerotyper — Fri, 11 Nov 2022 16:33:28 GMT

In my file, even selecting a date range with the slicer, also works.

Would you please share your file, so that can find out the reason?

Re: How to show all user ids which were NOT active during a sliced date range

WhoCares535454 — Fri, 11 Nov 2022 18:20:45 GMT

Sorry, I can't share the file itself due to security reasons. However, I have figured out what's causing it to go blank: I have a relationship between table1 and table2. It's a one to many relationship going both ways. When I slice the visual (using table1 data) by the date range (using table2 data), it tries to only return the data which only appears in that date range, and also only the data that doesn't appear in the date range at all. The result is it returns no data. If I deactivate the relationship, it works perfectly. I could keep the relationship inactive and just transplant all of my table1 data to table2 using lookupvaluecolumns, but I don't suppose there's a way to keep the relationship active? Thanks a lot for your help, just having the measure is very useful.

Edit: Update, it works so long as the relationship is only one way (from table1 to table2), which I think I can probably work with. Thanks a lot for all your help.

Re: How to show all user ids which were NOT active during a sliced date range

FreemanZ — Sat, 12 Nov 2022 03:40:32 GMT

The result you expect is a table, be it calculated table or visual table.

1) A calculated column or table can't reflect a slicer change.

2) There might be ways to write some measure to build a visual table for your case. I am not so sure how.