cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
molloyky Frequent Visitor
Frequent Visitor

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 5
Community Support Team
Community Support Team

Re: Search for multiple selections from filter

HI @molloyky,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
molloyky Frequent Visitor
Frequent Visitor

Re: Search for multiple selections from filter

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.

Community Support Team
Community Support Team

Re: Search for multiple selections from filter

Hi @molloyky,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
molloyky Frequent Visitor
Frequent Visitor

Re: Search for multiple selections from filter

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?

Community Support Team
Community Support Team

Re: Search for multiple selections from filter

Hi @molloyky,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)