DAX Measure Question

[chicagolarsons]

Hi,

First time poster, relatively new (but not brand new) to PowerBI. I am create a marketing dashboard for my company, and I am stuck on creating a measure that shows a count of new opportunities by region in the past 30 days.  I have got the time filter right, and I didn't think I would need to put anything about the regions in the measure, but the results are telling me otherwise.  So here are some details so that wiser minds can help me.

The tables are Calendar (date : creation date) CRM Opportunity (organization : Org ID)  CRM Region

The Measure is:

New Opps Last 30 Days = CALCULATE(COUNT('CRM Opportunity'[Opportunity Code]),FILTER(ALL('CRM Opportunity'),'CRM Opportunity'[Creation Date] >TODAY()-30 && 'CRM Opportunity'[Creation Date]<= TODAY()))

When I put it in a table, as shown below, it's summing all the new opportunities in the last 30 days correctly, but not discerning any difference between the regions.  In the details table, below that, you can see the break between Canada and Central.  There are 21 opportunities for Canada, 20 for Central, etc.  I assumed the table and the measure would work together, but clearly I'm wrong. Can anyone tell me what I'm doing wrong, and how to do this correctly?  I'd really appreciate it!

Thanks,

Kirstin

[Greg_Deckler · Accepted solution]

@chicagolarsons You need ALLEXCEPT instead of ALL and in the ALLEXCEPT specify your Region column.