topic Re: Add slicer to a filtered measure in DAX Commands and Tips

Add slicer to a filtered measure

Anonymous — Wed, 21 Aug 2024 16:29:30 GMT

I have a situation where I have 5 vendors that each have the same four regions (North, East, West, South). I have a simple table that has the vendor_name, region_name, and devicecnt fields that is updated nightly for current data only. On my dashboard, I had created individual KPI style cards for each vendor that shows their current counts, each with their own measure with the vendor name changed:

Ttl_Device_Cnt_Vendor1 = calculate (
SUM('Counts'[DeviceCnt]),
FILTER (
ALL ( 'Counts' ),
('Counts'[vendor_name]) IN { "Vendor1" }
))

this works great for each individual vendor, but now I want to add another multi-select slicer for regions so users can interact with other visuals on the page. I would like to have the region slicer affect these vendor measures, so I used Dax Studio to build a dax query:

Ttl_Device_Cnt_Vendor1 =
SUMMARIZECOLUMNS(
'Counts'[DeviceCnt],
KEEPFILTERS( TREATAS( {"North","East","West",South"}, 'Counts'[region_name] )),
KEEPFILTERS( TREATAS( {"Vendor1"}, 'Counts'[vendor_name] ))
)

This works when there is only one region selected on the slicer, but returns an error when more than one slicer value is chosen:

MdxScript(Model) (42,1) Calculation error in measure [Ttl_Device_Cnt_Vendor1]; A table of multiple vales was supplied where a single value was expected.

Please help me updated my Dax measure to keep the vendor name filter, but be able to handle more than one region slice value.

Thank you in advance!

Re: Add slicer to a filtered measure

mark_endicott — Wed, 21 Aug 2024 16:54:13 GMT

Anonymous - If you change your original measure to:

Ttl_Device_Cnt_Vendor1 = CALCULATE ( SUM('Counts'[DeviceCnt]), KEEPFILTERS('Counts'[vendor_name]) = "Vendor1" ))

Then it will keep the filter context of "Vendor1" but still allow all other filter contexts, such as selections in the region slicer to affect its value.

If this works for you, please accept as the solution.

Re: Add slicer to a filtered measure

Anonymous — Wed, 21 Aug 2024 17:29:33 GMT

Thank you for responding! unfortunately I am receiving a new error now:

KEEPFILTERS function can only be used as a top level filter argument of CALCULATE and CALCULATETABLE or with a table argument of a function performing a table scan.

Here is the updated DAX:

Ttl_Device_Cnt_Test = CALCULATE ( SUM('Counts'[DeviceCnt]), KEEPFILTERS('Counts'[vendor_Name]) = "Vendor1" )

Re: Add slicer to a filtered measure

mark_endicott — Thu, 22 Aug 2024 08:17:17 GMT

Anonymous - Where are you attempting to use this DAX? It should work fine in a measure.

Here is some more guidance on the use of KEEPFILTERS: https://www.sqlbi.com/articles/using-keepfilters-in-dax/

If this doesn't help, I'll need some more information, perhpas a screenshot and some information about your model.

If this works for you, please accept as the solution.

Thanks,

Mark

Re: Add slicer to a filtered measure

Anonymous — Fri, 23 Aug 2024 06:12:52 GMT

Hi Anonymous ,

Maybe you can modify your formula like below:

Ttl_Device_Cnt_Vendor1 = VAR SelectedRegions = SELECTEDVALUE('Counts'[region_name]) VAR SelectedVendor = "Vendor1" RETURN CALCULATE( SUM('Counts'[devicecnt]), FILTER( ALL('Counts'[region_name]), 'Counts'[region_name] = SelectedRegions ), 'Counts'[vendor_name] = SelectedVendor )

Best Regards,
Adamk Kong

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

Re: Add slicer to a filtered measure

mark_endicott — Fri, 23 Aug 2024 09:26:53 GMT

Anonymous - This is wrong.

1) SELECTEDVALUE will not accept the input of multiple Regions (should this be necessary) leading to a Blank result. And, if no region is selected there will be a Blank result also.

2) KEEPFILTERS is more optimal, and will respect the filter context applied by any other field in the model.

3) It will not solve Anonymous 's issue, which appears to be that they are using my solution in the wrong place.

Re: Add slicer to a filtered measure

Anonymous — Fri, 23 Aug 2024 14:02:06 GMT

Mark, I was finally able to get your solution to work after fixing a tiny syntax error in it:

Ttl_Device_Cnt_Vendor1 = 

CALCULATE (
SUM('Counts'[DeviceCnt]),
KEEPFILTERS('Counts'[vendor_name]) = "Vendor1" ))

has an extra right-parenthesis after 'counts'[vendor_name] that once removed, allows the region slicers to work as expected.

this works for me:

Ttl_Device_Cnt_Test =
CALCULATE (
SUM('counts'[DeviceCnt]),
KEEPFILTERS('counts'[vendor_name] = "vendor1" ))

Thank you so much for your patience and assistance! truly appreciated!!

Re: Add slicer to a filtered measure

mark_endicott — Tue, 27 Aug 2024 08:46:40 GMT

Anonymous - Ah yes, I see where I'd accidentally put the additional ")". Sorry for that, glad you reached the solution.