topic Need help with writing DAX in DAX Commands and Tips

Need help with writing DAX

Anonymous — Tue, 23 Nov 2021 13:51:53 GMT

Hi All,

Can you please help me write dynamic DAX measure? I have the date and Email address column in a table. I want to find how many emails were sent during a given period and categorize them accordingly. I am using a date in a filter and by changing the date, I can change the category ( i.e. number of emails sent) in that given period.

For instance, in Jan-2020 two email was sent and it will be categorized as 2 emails. And when I change the filter from month to year it gives me an email sent in a year to the customer.

Thank you so much for any given any help!

Date	EmailAddress	Email Sent/Year
10-Jan-20	A@com	4
20-Jan-20	A@com	4
21-Jul-20	A@Com	4
21-Mar-20	A@com	4
22-Mar-21	A@com	3
2-Apr-21	A@com	3
3-Apr-21	A@com	3

Re: Need help with writing DAX

Greg_Deckler — Tue, 23 Nov 2021 14:01:41 GMT

Anonymous Maybe:

Email Sent/Year = VAR __Year = YEAR(MAX('Table'[Date])) VAR __EmailAddress = MAX('Table'[EmailAddress]) VAR __Table = FILTER(ALL('Table'),YEAR('Table'[Date])=__Year && 'Table'[EmailAddress]=__EmailAddress) RETURN COUNTROWS(__Table)

Re: Need help with writing DAX

PaulOlding — Tue, 23 Nov 2021 14:02:41 GMT

Hi Anonymous

The DAX measure doesn't need to be complicated for this:

Emails Sent = COUNTROWS(Table)

You can then construct a visual (eg matrix of email vs year) or combination of visuals (eg date slicer and table with email & Emails Sent) to get the insight.