topic Re: How to use DAX expression to find all distinct ids between certain dates in DAX Commands and Tips

How to use DAX expression to find all distinct ids between certain dates

Anonymous — Thu, 13 Jan 2022 17:42:05 GMT

Hello, I just have a quick question.

So, we have a table called "Not_messaged' that has information about candidates. These fields are lastMessageDate (Date field), id (integer field), NotesMessageDate (Date field), and CallNotesLogDate (Date field).

what we want is to return a distinct count of ids that:

Do NOT have messagedLastSentDate before today's date
OR DOES NOT have callNotesLogdate = todays date
OR DOES NOT have NotesLogDate = today's date

I am trying to come up with a DAX expression that can return a distinct count of the ids. However, we are getting stuck because we're not good at DAX expressions.

Re: How to use DAX expression to find all distinct ids between certain dates

Syk — Thu, 13 Jan 2022 17:53:22 GMT

I'd use the distinctcount function for DAX and then just use visual level filters to achieve what you're looking for

So something similar to

DISTINCTCOUNT('Not_messaged'[id])

Re: How to use DAX expression to find all distinct ids between certain dates

Anonymous — Thu, 13 Jan 2022 18:03:40 GMT

I see, for the visual filters, can you do IF/OR statements?

Re: How to use DAX expression to find all distinct ids between certain dates

smpa01 — Thu, 13 Jan 2022 18:06:32 GMT

Anonymous please provide sample data and desired output.

Re: How to use DAX expression to find all distinct ids between certain dates

Syk — Thu, 13 Jan 2022 19:00:50 GMT

Seems like you just don't want today's date. You can add a date filter to the visual (or page/report)! Attached is an example of a date filter using the relative date filter type.

Re: How to use DAX expression to find all distinct ids between certain dates

AlexisOlson — Thu, 13 Jan 2022 20:55:56 GMT

You can put as many conditions as you want in a FILTER.

CALCULATE ( DISTINCTCOUNT ( Not_messaged[id] ), FILTER ( Not_messaged, Not_messaged[lastMessageDate] >= TODAY () || Not_messaged[callNotesLogdate] <> TODAY () || Not_messaged[NotesLogDate] <> TODAY () ) )