topic Re: Quick Measure and Slicers in DAX Commands and Tips

Quick Measure and Slicers

FormworkFan — Sat, 07 Oct 2023 16:03:45 GMT

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.

Re: Quick Measure and Slicers

Dangar332 — Sun, 08 Oct 2023 20:24:33 GMT

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.

Re: Quick Measure and Slicers

FormworkFan — Sun, 08 Oct 2023 21:37:05 GMT

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"

Re: Quick Measure and Slicers

Dangar332 — Mon, 09 Oct 2023 13:24:42 GMT

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"

Re: Quick Measure and Slicers

FormworkFan — Mon, 09 Oct 2023 12:58:00 GMT

No Luck on that.

Re: Quick Measure and Slicers

Anonymous — Tue, 10 Oct 2023 06:55:10 GMT

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

Re: Quick Measure and Slicers

FormworkFan — Sun, 15 Oct 2023 16:58:06 GMT

Hi,

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

Re: Quick Measure and Slicers

FormworkFan — Fri, 20 Oct 2023 19:38:16 GMT

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