Filter data based on a comma separated list using CALCULATE and PATHCONTAINS, even with external models!

Another interesting question I got this week kind of extends on the previous blog posts where I used disconnected slicers to filter data  but this time instead of having the data already in the model the request was to allow to write a filter based on a comma separated list. So off we go again into the DAX rabbit hole 

I decided I wanted to extend the previous example with this question. I created a new matrix visual and added the measure that shows the sales based on the data from the slicer selected (as discussed in the previous blog post). Next I added a measure that contains the list they want to filter. So now in this case I want to see sales for Colors Red, Green, Gold as entered in the comma separated list:

Next I need to write a measure that turns that comma separated list into a filter argument. As you might remember DAX actually contains some  functions that allows us to work with comma separated lists to do parent child using functions like PATH and PATHCONTAINS. We can use those to determine if rows need to be filtered or not.  To get the color list and put that on the filter context I create the following DAX expression:

Measure = var colorslist = TRIM(SUBSTITUTE(SUBSTITUTE([ColorList], ",","|")," ",""))
          var tableval = CALCULATE(SUM(FactOnlineSales[SalesAmount]),PATHCONTAINS(colorslist , DimProduct[Colorname]))
          return tableval

This measure uses variables for readability. The colorlist variable is used to get the values of the colorlist and then replace the , for | characters as needed for the path functions, then I trim and clean up the spaces. using TRIM and SUBSTITUTE. The result is returned in the colorlist variable. Next I use our favorite function CALCULATE to determine whether or not each row in the DimProduct[Colorname] is part of the colorlist, the PATHCONTAINS function will return TRUE whenever the value of the colorname is part of the colorlist list and thus calculate the SUM for those colors.

This works like a charm:

I made sure the slicer selection is the same as the comma separated list to test the numbers. Now if we change it to “Red, Gold ” to see if it works:

and it does. So voila now you can let users type in their selection into a measure.

Now this only works when you have access to the model but you can also do this using Power BI desktop pointing to an external model (either SSAS or a model already hosted in Power BI)  in the same fashion. Now your users can connect to an external model and create their own custom selections!

I have uploaded the workbook here.