Hi,
I need help writing an expression that counts the rows in a table, but excludes rows from the count when there is a duplicate date in the date column. I also need to further constrain the results by the Client ID for the current record, i.e. [_THISROW].[Client ID] = [Client ID]. I can do one or the other, but I havenโt figured out an expression that will do both.
Thanks!
Solved! Go to Solution.
Count of distinct date-column
column values in the table
table from rows for this client:
COUNT(
SELECT(
table[date-column],
([_THISROW].[Client ID] = [Client ID]),
TRUE
)
)
Meaning only count the first row with a given date, or omit any row with a date that occurs more than once?
Correct
Count of distinct date-column
column values in the table
table from rows for this client:
COUNT(
SELECT(
table[date-column],
([_THISROW].[Client ID] = [Client ID]),
TRUE
)
)
Iโll give that a shot, thanks!
Looks like that did the trick, thanks again.
Hi Steve,
Follow up question: I need to add another constraint to this expression, which I attempted to do, like this:
COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID], TRUE)))
However now it is no longer omitting rows with a date that occurs more than once. Iโm using this in a virtual column and need a formula that counts only once each date that a user billed to a client (sometimes more than one user bills in a given day, sometimes the same user more than once). Any thoughts on how I can make this work? Thanks!
Your expression on the first line, my suggestion on the second:
COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID], TRUE)))
COUNT(SELECT(Invoice Detail[VisitDate], AND([_THISROW].[Client ID] = [Client ID], [_THISROW].[User ID]=[User ID]), TRUE))
Itโs still counting duplicate dates. Could it be an issue with the way the date is formatted?
If the dates have different formats, sure, that could be an issue.
User | Count |
---|---|
37 | |
30 | |
29 | |
22 | |
18 |