Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Search for multiple selections from filter

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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