Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
basrooz
Advocate I
Advocate I

DAX query to get the opposite of a filtered value

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

 

screenshot.PNG

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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))

Eyelyn9_0-1636422205093.png

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.

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Could you clearly show the end result you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-eqin-msft
Community Support
Community Support

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))

Eyelyn9_0-1636422205093.png

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.

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

Best regards

Bas

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
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.