Best way to get multi select slicer to work with IF Statement

[Dexter1088]

Hi All, 

Running into a what I think should be a simple solution, but having issues.

I have a cusom table for category options that I use for my slicer.
When you select the category, I have a measure that uses a IF statement to pull the LAT and LONG for the locations that fall under that category for my map. 
This works perfectly if I enable single select, but I need it to work with multi select as well.

I tried something like this to see what all is selected, but can't seem to figure out how to translate it to a IF statement without writing all 511 possibilities of selections lol.

a_SelectedCAT = 
IF(
    ISFILTERED(Cat_Picker[Column1]),
    CONCATENATEX(DISTINCT(Cat_Picker[Column1]),Cat_Picker[Column1],"|"),"")

Any ideas or help definantly appreciated, thanks!

[Anonymous]

Hi, @Dexter1088 

Based on your description, I've created the following sample data:

I created a measure:

FilteredLATLONG = 
IF(
    ISFILTERED(Cat_Picker[Column1]),
    CALCULATE(
        CONCATENATEX(
            'LocationTable',
            'LocationTable'[LAT] & "," & 'LocationTable'[LONG],
            ";"
        ),
        'LocationTable'[Category] IN VALUES(Cat_Picker[Column1])
    ),
    BLANK()
)

a_SelectedCAT = 
IF(
    ISFILTERED(Cat_Picker[Column1]),
    CONCATENATEX(DISTINCT(Cat_Picker[Column1]), Cat_Picker[Column1], "|"),
    ""
)

Here are the results:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Dexter1088]

Very well written thank you! trying to make this work with my current set up is posing issues though, let me explain more, to see if there is an easier way, thanks!

Here are my table setups going off your example, My Location table is unique to the location and the categories are built out in columns. 

My Category picker table is the same as yours

And I forgot to mention I have a seoerate measures for LAT and LONG so I can put them in the Azure map like so.

I can get this to work if you select only 1 category at a time, I just cant get it to plot on a map if you select more than 1 category. Like sometimes we want to see locations that have Food and Health only on the map, thanks!

[Anonymous · Accepted solution]

Hi, @Dexter1088

 Thank you very much for your reply. I created a table with your data:

First, reference this table:

The results are as follows:

I have included the PBIX file used for this tutorial below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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