How to tag selected rows of data

[Anonymous]

I'm posting this issue because I'm not sure how to do this in Power BI.

I want to tag some rows based on certain conditions. I can do this using calculated columns. However, the issue with calculated column is that only a single value can correspond to each row. This is why I need tagging. With tagging, each row can have several tags as long as each of those conditions satisify.

Simple Example:

I want to tag those that contain "sunny" or "windy". If both availabe, tag them for both.

No.	Text	Category
1	sunny and cool	sunny
2	rainy and windy	windy
3	snowy and freezy
4	sunny and windy	sunny

The Category column is obtained by this dax:

Category = SWITCH(TRUE(),

CONTAINSSTRING('Table'[Text], "sunny"), "sunny",

CONTAINSSTRING('Table'[Text], "windy"), "windy")

However, this is wrong. That is, for the last entry, it should be tagged for both "windy" and "sunny". But, calculated column is not able to do that and only "sunny" is shown in the calculated column.

Goal: I finally want to use "Category" column as a slicer for filtering the whole visuals in the dashboard.

If I use Category as a filter: 

      If I choose "sunny" in the filter, it returns rows 1 and 4.

      If I choose "windy" in the filter, it returns rows 2 and 4.

Can you please help me with this? How can Power BI do data tagging?

[v-janeyg-msft]

Hi, @Anonymous 

According to your description, I think you can create a single table column as slicer, then create a measure and put it in filter pane to filter data.

Like this:

Measure = 
VAR a =
    IF (
        CONTAINSSTRING ( MAX ( 'Table'[Text] ), "sunny" )
            || CONTAINSSTRING ( MAX ( 'Table'[Text] ), "sun" ) = TRUE,
        1,
        0
    )
VAR b =
    IF ( MAX ( 'Table'[Wind] ) = "high", 1, 0 )
VAR c =
    IF ( MAX ( 'Table'[Temp] ) = "low", 1, 0 )
RETURN
    IF (
        ISFILTERED ( Table2[Category] ),
        IF (
            SELECTEDVALUE ( Table2[Category] ) = "tennis",
            a,
            IF (
                SELECTEDVALUE ( Table2[Category] ) = "surfing",
                b,
                IF ( SELECTEDVALUE ( Table2[Category] ) = "ski", c )
            )
        ),
        1
    )

Sample is below.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

@v-janeyg-msft 

Wow! Very genius solution! Thank you very much. 

The measure works well when I use it as a filter on the table visual.

However, it seems that I cannot apply the filter measure on the whole page or on some visuals such as cards.

Do you have any solution for this? 

Thanks. 

[v-janeyg-msft]

@Anonymous 

Your needs are complicated, and it is impossible to achieve it in one step. What I provide is a workaround. If you have other needs, you also need to customize. Can you upload some insensitive data samples and expected output?

Best Regards,
Community Support Team _ Janey

[Anonymous]

I understand that my task is complicated. I appreciate your time and assistnace.

As a simple user, I am not able to upload file. My data and output are the same as before (shown below again).

Your solution was really good, but I am not able to apply it on the whole page or on some visuals (such as cards). This is the only issue. I am facing now.

Data and output:

No.	Text	Temp	Wind	Category
1	The shining sun and temp  were perfect to kick the ball.	medium	low	tennis
2	It was windy and cold.	low	high	surfing ski
3	I liked the snowy weather.	low	low	ski
4	The sunny weather was fantastic.	high	high	tennis surfing
5	Hell yea! The wind was blowing with snow falling from heaven.	low	high	surfing ski

I want to create a "Category" measure and use it as a filter slicer.

The condition and output for each selection of the slicer are shown below:

condition for "tennis":      CONTAINSSTRING('Table'[Text], "sunny") || CONTAINSSTRING('Table'[Text], "sun")

tennis ==> output:   rows 1 and 4

condition for "surfing":     'Table'[Wind] = "high"

surfing ==> output:   rows 2,4,5 

condition for "ski":         'Table'[Temp] = "low" 

ski ==> output:   rows 2,3,5

Thank you very much for your time.

[v-janeyg-msft]

@Anonymous 

Do you understand what I asked? Your needs need to be customized for each visual.

Your output returns rows. Obviously, only table or matrice visual can match. How do you return rows in card? It can only return values. And in the whole page, the context of each visual may be different, how to unify it? 

Measure is flexible and will change with the context. We can’t define it if you don’t make it clear.

Best Regards,
Community Support Team _ Janey

[Jihwan_Kim]

Hi,

I am not sure whether I understood your question correctly,  but please check the below picture and the attached pbix file.

Category CC =

SWITCH (

TRUE (),

AND (

CONTAINSSTRING ( 'Table'[Text], "sunny" ),

CONTAINSSTRING ( 'Table'[Text], "windy" )

), "sunny and windy",

CONTAINSSTRING ( 'Table'[Text], "sunny" ), "sunny",

CONTAINSSTRING ( 'Table'[Text], "windy" ), "windy"

)

[Anonymous]

Thanks for your reply.

What you built results in another category: "sunny and windy".

This is not what I want.

What I want is tagging rows. This means that there is another space that there are tags for rows. The tags are virtual or abstract. Like measures, contrary to calculated columns. 

Expected outcome: 

If I use Category as a filter: 

      If I choose "sunny" in the filter, it returns rows 1 and 4.

      If I choose "windy" in the filter, it returns rows 2 and 4.

I'm not sure how to do this in Power BI.

[Jihwan_Kim]

Hi

Thank you for your message.

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

Text Measure: =
VAR select_list =
VALUES ( Categories[Category] )
VAR result =
CALCULATE (
SELECTEDVALUE ( 'Table'[Text] ),
CONTAINSSTRING ( 'Table'[Text], select_list )
)
RETURN
result

[Anonymous]

Thank you so much.

It is interesting to see some creative nice solutions.

This is actually close to what I want, but not exactly.

Several issues:

1- I don't have a separate table for categories, and prefer not to have due to complications it causes in relations with other tables. I prefer the filter content be automatic.

2- I provided a simple example with "containsstring" condition. However, imagine I have several conditions to filter rows. Consider the following table

No.	Text	Temp	Wind	Category
1	The shining sun and temp  were perfect to kick the ball.	medium	low	tennis
2	It was windy and cold.	low	high	surfing ski
3	I liked the snowy weather.	low	low	ski
4	The sunny weather was fantastic.	high	high	tennis surfing
5	Hell yea! The wind was blowing with snow falling from heaven.	low	high	surfing ski

I want to create a "Category" measure and use it as a filter slicer.

Each selection of the slicer should return these rows:

tennis ==> rows 1 and 4 ==> code:      CONTAINSSTRING('Table'[Text], "sunny") || CONTAINSSTRING('Table'[Text], "sun")

surfing ==> rows 2,4,5 ==> code:     'Table'[Wind] = "high"

ski ==> rows 2,3,5 ==> code:         'Table'[Temp] = "low" 

These conditions mean that one row can have multiple labels and I want to be able to filter all related labels using the filter slicer.

Any help please?