DAX query to get the opposite of a filtered value

[basrooz]

Hi,

I have a three tables that are connected via a ‘country code’ id (there's also a connection to a date table and connections to other tables but let's stick with these three for now to keep it understandable). One of the tables is a ‘campaign’ table. Using several DAX queries I can then see if during the campaign period the amount of sales for a specific country went up or down. For instance in this example there’s a Discount promo campaign in NL. I can now see that this delivered x sales during the campaign period.

This is the calculation that is generating the amount of sales for all countries in the campaign period:

VAR start_date = MIN ( [Event start date] )
VAR end_date =   MAX ( [Event end date] )

RETURN

IF ( ISBLANK ( SELECTEDVALUE ( 'Event data'[EVENT NAME] ) ) , BLANK () ,
CALCULATE( [$ Sales] ,
'Date'[Date] >= start_date && 'Date'[Date] <= end_date
))

I struggle however with creating a DAX query that will show me the amount of sales of all the countries who DIDN'T had a campaign. For instance in the example you see that I have a Discount promo in NL, how can I add a filter to my calculations so that it only displays the results for all countries that are NOT NL?

Best regards

Bas

[Ashish_Mathur]

Hi,

Could you clearly show the end result you are expecting.

[v-eqin-msft · Accepted solution]

Hi @basrooz ,

According to your description, you want to calculate the sum for DE and GB since both don't exist in Campaign table ,right? 

If so, please try :

Measure = 
var _t= SUMMARIZE(FILTER('Country',NOT('Country'[Country code] in ALLSELECTED(Campaign[Country code]))),[Country code])
return CALCULATE(SUM(Sales[Sales]),FILTER('Sales',[Country code] in _t))

If it's not your expected, please share more detail information to help us clarify your scenario.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[basrooz]

@v-eqin-msft Thank you very much, this works perfect! Thank you so much for your help!

Best regards

Bas

[v-eqin-msft]

Hi @basrooz ,

Thanks for your feedback!

Could you please Accept my post as the solution ? More people will benefit from it.

Best Regards,
Eyelyn Qin

[MFelix]

Hi @basrooz ,

Have you tried just making a simple subtration to your value of salues something similar to:

Sales not in campaign = [$ Sales] - [$ Sales Campaing]

You do not show the name of the measure above so I assumed has Sales Campaing.

[basrooz]

@MFelix  nope that's wouldn't work. The sales table and the campaign table are connected via the country table. Every campaign has a specific country assigned. So if I add a table as a visual that shows me the campaign name and the sales from that campaign, then it automatically only shows me the sales for that campaign for that country.

And that's where the problem is at. I would like to know if there's some how a way to use the connection in the model in a way that it's doing the opposite, and showing me the total sales from all countries that did not had a campaign in that time period.

Hope this makes a bit sense!
Thanks
Bas

[MFelix]

Hi @basrooz ,

I understand your issue but not being abble to reproduce your model I have some questions:

• You are using a calendar table (Date)? This table is related with the sales correct?
• Is the DAX formula that you sent a calculated column or a measure?

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.