Search for multiple selections from filter

[Anonymous]

I am trying to count the number of issues in a table that include or do not include tags. I have created a formula that will count the number of issues that include or exclude a specific tag, but I cannot get it to work for multiple selections from the tag filter. The filter has single tags, but it searches the a column that is a concatenation of all tags on the issue. There is also a filter that decides if the tag should be included or excluded.

Total Tickets = if(max('Action'[Tag Action])="include",CALCULATE(count(Issues[Issue ID]),filter(Issues,SEARCH(SELECTEDVALUE('Banned Tags'[Tag]),Issues[Tags (concatenated)],,BLANK()))),if(max('Action'[Tag Action])="exclude",CALCULATE(count(Issues[Issue ID]),filter(Issues,NOT(SEARCH(SELECTEDVALUE('Banned Tags'[Tag]),Issues[Tags (concatenated)],,BLANK()))))))

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you use SWITCH function to get logical variable to use it in your formula.

Sample measure:

Total Tickets =
VAR logicAction =
    SWITCH (
        MAX ( 'Action'[Tag Action] ),
        "include", TRUE (),
        "exclude", FALSE (),
        FALSE ()
    )
RETURN
    CALCULATE (
        COUNT ( Issues[Issue ID] ),
        FILTER (
            Issues,
            SEARCH (
                SELECTEDVALUE ( 'Banned Tags'[Tag] ),
                Issues[Tags (concatenated)],
                ,
                BLANK ()
            )
                = logicAction
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you Xiaoxin Sheng for your suggestion, but I received the following error "calculation error in measure: DAX comparison operations do not support comparing values of type Integer with values of type True/False."

Additionally, I believe switch is just assisting with the ability to include or exclude the tag in the count. The if formula I used is accomplishing the ability to toggle between those two actions. The problem is when multiple tags are selected from the filter, the calcuation will not check for the two different tags.

[v-shex-msft]

Hi @Anonymous,

I modify formula to work through convert search function result as boolean:

Total Tickets =
VAR logicAction =
    SWITCH (
        MAX ( 'Action'[Tag Action] ),
        "include", TRUE (),
        "exclude", FALSE (),
        FALSE ()
    )
RETURN
    CALCULATE (
        COUNT ( Issues[Issue ID] ),
        FILTER (
            Issues,
            (
                SEARCH (
                    SELECTEDVALUE ( 'Banned Tags'[Tag] ),
                    Issues[Tags (concatenated)],
                    ,
                    BLANK ()
                )
                    >= 1
            )
                = logicAction
        )
    )

>>The problem is when multiple tags are selected from the filter, the calcuation will not check for the two different tags.

Yes, SWITCH function not works for summary contents, so it not works on multiple selections. I don't think your tables contains rows who can include and exclude specific characters at same time

Regards,

Xiaoxin Sheng

[Anonymous]

The updated formula you provided works when a single selection is made, but not when multiple selections are made in the tag filter. Are you saying there is no way to create a formula that will check for multiple tags?

[v-shex-msft]

Hi @Anonymous,

I think it is hard to achieve search operation between list and text value, can you please provide some of sample data to test?

Regards,

Xiaoxin Sheng