Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Using the attached sample data, how can I find out which usernames have WorkingDayDisconnects as 'true' for ALL sessions and also for which the DisconnectionReason reason is only ever blank or 'CLOSED_BY_CLIENT'
i.e. if WorkingDayDisconnect is 'true' even once for a username, it needs to be excluded from the results. Similarly, if the DisconnectionReason is anything other than blank or 'CLOSED_BY_CLIENT' for all session, then it too must be excluded.
How do you want the results to display?
The below expression can be added to a straight table chart with the dimension of [UserName] to detect who has 1 or more trues:
=IF(COUNT({1<[WorkingDayDisconnect] = {'true'}>} [WorkingDayDisconnect] > 0,'At least one true','All false')
If you don't want the UserName's with "trues" to show up at all you can use the following:
=IF(COUNT({1<[WorkingDayDisconnect] = {'true'}>} [WorkingDayDisconnect] = 0,'All false')
The expression would be similar for the disconnection reason.
Try using exists() function in where conditions, maybe like
LOAD DISTINCT
UserName as ExcludeUsername
FROM
[sample disconnects.xlsx]
(ooxml, embedded labels, table is Sheet1)
where WorkingDayDisconnect = 'true' or (len(trim(DisconnectionReason)) and DisconnectionReason <> 'CLOSED_BY_CLIENT');
LOAD UserName,
SessionStartedDate,
WorkingDayDisconnect,
DisconnectionReason
FROM
[sample disconnects.xlsx]
(ooxml, embedded labels, table is Sheet1)
where not exists(ExcludeUsername, UserName);
just a simple list of the usernames which meet that criteria. The usernames which don't would be excluded from the results
This doesn't remove the duplicates though. I need unique values displayed
What other dimensions do you have in your table?
There are no other dimensions. I need a simple list of usernames. That's it