Super easy question: Create a column with filtered data

[HarryT]

Hi there,

We just switched from Qlik to PBI.

Here is my question that I can use for most of my work.

I have this expression in Qlik: 

Count({<eventtype={'Sent'}>} distinct emailaddress) - Count({<eventtype={'Bounce'}>} distinct emailaddress)

I want to make a new column in PBI that can do the same calulation.

Even just make a basic one like:

Sent = Count({<eventtype={'Sent'}>} distinct emailaddress) 

column name in PBI is email_stats[eventtype]

Trying to understand how DAX will do this.

I appreciate your assistance.

[Cmcmahan · Accepted solution]

Just to double check, you're trying to get a count of email recipients, minus the number of recipients that bounced. 

Are you trying to add this result to each row of the table (maybe counting all values where the sender is the same), or just get a current count of non-bounced sent items to display on a dashboard?   I've never used Qlik, so the answers to this may be apparent from the syntax there.

To start, I'll show you how to create a measure to display this as a single value in a report. If you have a different need, just reply with more info like table structure and intended result for a more precise answer. You would create a measure like this with DAX:

Non-bounced = CALCULATE( DISTINCTCOUNT('email_stats'[recipient_emailaddr]), 'email_stats'[eventtype] = "Sent") -
              CALCULATE( DISTINCTCOUNT('email_stats'[recipient_emailaddr]), 'email_stats'[eventtype] = "Bounce")

So to explain a little bit about what's happening here.  DISTINCTCOUNT([column]) is pretty straightforward and gets a distinct count of recipient email addresses.  However, we only want to count SOME of the rows, so we wrap it in a CALCULATE.  CALCULATE(<expression>,<filters>) lets us apply filters to the expression.  The filter we're applying first would essentially read like WHERE eventtype="Sent" in SQL.

So the first line calculates a distinct count of email addresses where the eventtype is sent, and then subtracts the second line which calculates the distinct count of email addresses where the eventtype is bounce.  

One of the great things about this measure, and PowerBI in general, is that if you put this on a page, and then later set up a filter/slicer on the report to only display data from a given date range, or only display data where the recipient has an @gmail.com address, the measure will automatically update with those constraints applied.

[HarryT]

Thanks that is exactly it.

I don't want a new column I want a new measure like your example to put in a summerizes table view.

It worked perfectly and I understand it better now.

Thanks!

[Anonymous]

Can you explain the function you want performed? Additionally you may find this website helpful as it explains the expressions and functions of DAX and how to use them. 

https://dax.guide/