cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Perform conditional DAX in Slicer

Dear Community,

 

I have a 4 column in my table, which is Country, Code, Profit and Revenue.

CountryCodeProfitRevenue
PVGABC10004000
INDDEF20005000
UKQWE30006000

 

So i will create a table in my report, which will sum all the particular country's profit and revenue. 
Here my question, is that possible if I select PVG in my slicer, it will auto filter out the code 'ABC' and 'DFE' then sum their profit and revunue. Moreover, if I select IND, it will filter out 'QWE' and 'DEF' code. 

* IND need to have 'ABC' and 'DFE' code in the calculation, only excluded for PVG.

 

I thank you for taking time out to answer my question. I am sure that you are really busy and I appreciate you taking the time to respond personally to me. Thank you again.

Best regards.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Perform conditional DAX in Slicer

@NickProp28 ,

Try to revamp it like below code:

Final Measure = Switch(True(), MAX(Table[County])="PVG",Calculate( Measure, Table[Code] NOT IN {"ABC", "DEF"}), Table[County]="IND", Calculate( Measure, Table[Code] IN {"ABC", "DEF"}))

 

NOTE: Measure is the name of calulcated profit which you just posted above.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: Perform conditional DAX in Slicer

@NickProp28 

Your example doesn't follow a pattern or a logic, can you explain with a logic that could be applied not only to those sample set but to a full data set?

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS  to replies that help answer your questions


Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

Highlighted
Super User III
Super User III

Re: Perform conditional DAX in Slicer

Your requirement is not very much clear. Could you please elaborate more?  It's better to share some expected output  with proper numbers.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
Highlighted
Helper V
Helper V

Re: Perform conditional DAX in Slicer

Dear @Tahreem24 @Fowmy ,

 

I'm sorry if its confusing you. Im still beginning to Power BI.
So, simplify it. I have import data from database server, and there have Country, Code, Profit and Revenue.

Then, I make a report and use slicer to let user choose which Country data they would like to see. The questions is one country have plenty of code, and some of the code i do not want to be calculate in the profit/revenue. (Like PVG country have code with 'ABC', i do not want to be calculate).

 

But if user select IND, code in 'ABC' will be count. 

Hope I have clear your doubt. Once again thank for the time answering my question. 

Highlighted
Community Champion
Community Champion

Re: Perform conditional DAX in Slicer

@NickProp28 

If you need to exclude certain combinations of Countries and Codes in your calculations, then you need to have those countries and codes in a separate table then its possible to create measures.

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS to replies that help answer your questions


Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

Highlighted
Helper V
Helper V

Re: Perform conditional DAX in Slicer

Dear @Fowmy ,

 

Thanks for your prompt response, but im not quite understood. Can you please explain more, and possible to have some coding let me get the whole picture idea?

Highlighted
Super User III
Super User III

Re: Perform conditional DAX in Slicer

Try to use the below code:

Measure = Switch(True(), Table[County]="PVG",Calculate( Sum ( Table[ Revenue]), Table[Code] NOT IN {"ABC", "DEF"}), Table[County]="IND", Calculate( Sum ( Table[ Revenue]), Table[Code] IN {"ABC", "DEF"}))

Like this with the help of IN and NOT IN operator please fix your requirement. 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report
Highlighted
Helper V
Helper V

Re: Perform conditional DAX in Slicer

Dear @Tahreem24 ,

 

Thank you for your response on my question. I adjust my database and decided add on two column. So now have Country,Origin,Destination,Profit and Revenue.

 

My calculation for Profit is

Measure= Calculate(SUM(Table[Profit],filter(table,table[origin]<>table[destination] && Left(table[origin],2) IN {"AE", "YE'} &&LEFT(table[Destination],2) IN {"AM","GE"})). May i get advise from you how do I add your switch DAX in my current DAX. Sorry that i so bad in code logic.

 

Best thanks.

Highlighted
Super User III
Super User III

Re: Perform conditional DAX in Slicer

@NickProp28 ,

Try to revamp it like below code:

Final Measure = Switch(True(), MAX(Table[County])="PVG",Calculate( Measure, Table[Code] NOT IN {"ABC", "DEF"}), Table[County]="IND", Calculate( Measure, Table[Code] IN {"ABC", "DEF"}))

 

NOTE: Measure is the name of calulcated profit which you just posted above.

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
You can follow me on LinkedIn

Please take a quick glance at newly created report:
My Report

View solution in original post

Highlighted
Community Support
Community Support

Re: Perform conditional DAX in Slicer

Hi , @NickProp28 

Do you have resolved it? If yes, you could accept the helpful answer as solution. You also could share your own solution here. For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors