Forum Discussion
Post PartisanPerform 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.
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.
10 Replies
- Fowmy
Super User
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 
Tahreem24Your requirement is not very much clear. Could you please elaborate more? It's better to share some expected output with proper numbers.
NickProp28I'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.
- Fowmy
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
- Tahreem24
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.
- NickProp28
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.
- Tahreem24
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.
- v-easonf-msft
Community Support
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- NickProp28
Dear Tahreem24 v-easonf-msft,
Sorry for my late response, its help. But last time I had accept it as solution, might due to my internet issue and cause pages does not run completly.Thank again Tahreem24 .
