Perform conditional DAX in Slicer

[NickProp28]

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.

[v-easonf-msft]

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 .

[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 · Accepted solution]

@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.

[Tahreem24]

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

[NickProp28]

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. 

[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

[NickProp28]

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?

[Fowmy]

@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