Quick Measure and Slicers

[FormworkFan]

I'm trying to get my slicer to Filter out some Sales Revenue in Canadian dollors. My Goal is to to display any Sales outside of the local Currency.  I created a quick measure "Filter by Value", but I need to get a way to have this based on my Region Slicer.  screen shots and code is below.  Ideally when I check off Canada on the Slicer the vale of the Canadain Dollor box should be $0.00

As you can tell I'm no expert, but would appreiacte any feed back on this.

[v-yangliu-msft]

Hi  @FormworkFan ,

Here are the steps you can follow：

1. Create measure.

True =
var _select=SELECTCOLUMNS('Combined Divison Report',"1",'Combined Divison Report'[Region])
var _column=CONCATENATEX(FILTER(ALL('Combined Divison Report'),'Combined Divison Report'[Region] in _select),'Combined Divison Report'[Region],",")
return
IF(
    CONTAINSSTRING(
        _column,"Canada") =TRUE(),
        SUMX(
            FILTER(ALL('Combined Divison Report'),
            'Combined Divison Report'[Region] in _select && CONTAINSSTRING('Combined Divison Report'[Region],"Canada") =TRUE()),'Combined Divison Report'[42000 -Sales Revenue Amount]),0)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[FormworkFan]

Your Solution works, unitl I add a Date Slicer into my Report.  It is taking in all of the Data and will not filter by Date.  How can I get these new Measure to work with my Date Slicer?

thanks

[FormworkFan]

Hi,

This seems to be working as I need. I'm still playing around with it though.

[Dangar332]

hi, @FormworkFan 
try below measure

canadian sales =

if( selectedvalue('yourtable'[region])="canada",calculate( sum('combined division report'[420000 - sales revenue amount])),0.00)

Did i answer your question? Mark my post as a solution which help other people to find  fast and easily.

[FormworkFan]

Thank you for the reply and help ont this

This Kind of works when I only have that one Region "Canada" Selected on my slicer. If I have Multiple Regions selected including the Canada Region, the value is coming as $0.00.

This result is kind of giving me the opposite of what I had at the start.

I placed  some screen shots below and as an FYI I broke my Canada into two Regions (East and West). So Will need to add this into there some how. perhaps like an OR Statement, so if I select Eastern or Western or both.

  yourtable'[region])="Eastern Canada"

[Dangar332]

hi, @FormworkFan  @FormworkFan 

 try below

if( selectedvalue('yourtable'[region])=CONTAINSSTRING(values(yourtable'[region]),"canada"),calculate( sum('combined division report'[420000 - sales revenue amount])),0.00)

use only "canada" instead of "easteran canada"

[FormworkFan]

No Luck on that.