cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickProp28
Post Partisan
Post Partisan

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

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

10 REPLIES 10
v-easonf-msft
Community Support
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

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

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 dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

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.

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

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

View solution in original post

Tahreem24
Super User
Super User

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 dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, and Sales Analysis Report

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. 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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