cancel
Showing results for
Did you mean:
Highlighted
Helper V

## Perform conditional DAX in Slicer

Dear Community,

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

 Country Code Profit Revenue PVG ABC 1000 4000 IND DEF 2000 5000 UK QWE 3000 6000

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

## Re: Perform conditional DAX in Slicer

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

Please take a quick glance at newly created report:
My Report
10 REPLIES 10
Highlighted
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

Highlighted
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!!!

Please take a quick glance at newly created report:
My Report
Highlighted
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

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

Highlighted
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

## 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!!!

Please take a quick glance at newly created report:
My Report
Highlighted
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

## Re: Perform conditional DAX in Slicer

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

Please take a quick glance at newly created report:
My Report
Highlighted
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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021