Add slicer to a filtered measure

[Carthen]

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!

[mark_endicott · Accepted solution]

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

[Anonymous]

Hi @Carthen ,

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.

[mark_endicott]

@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 @Carthen 's issue, which appears to be that they are using my solution in the wrong place. 

[Carthen · Accepted solution]

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

[mark_endicott]

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

[Carthen]

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" )

[mark_endicott]

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

[mark_endicott · Accepted solution]

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