Conditional formatting per group

[dataforlife]

I am trying to do conditional formatting per group with the following logic, but it does not seem to work.

The logic of my measure should be as follows: if group x has y amount of work, then it should be labeled either green, orange or red.

When I exclude the team strings, it does seem to work, but the combination of numerical logic (e.g. > 0 && < 18) and then choosing the right team seems to make the measure not work.

I am also not able to select it as a measure for my conditional formatting (only if I change the measure data type to text).

Color measure =
VAR Team =
    SELECTEDVALUE ( Team[Name] )
RETURN
    SWITCH (
        TRUE (),
        Team = "South"
            && 'Measures'[Work Total] >= 0
            && Measures'[Work Total] < 18, "#09D543",
        -- Green
        Team = "South,"
            && Measures'[Work Total] >= 18
            && Measures'[Work Total] < 36, "#FFA500",
        -- Orange
        Team = "South"
            && Measures'[Work Total] >= 36, "#FF0000",
        -- Red
        Team = "North"
            && Measures'[Work Total] >= 0
            && Measures'[Work Total] < 10, "#09D543",
        -- Green
        Team = "North"
            && Measures'[Work Total] >= 10
            && Measures'[Work Total] < 20, "#FFA500",
        -- Orange
        Team = "North"
            && Measures'[Work Total] >= 20, "#FF0000",
        -- Red
        Team = "West"
            && Measures'[Work Total] >= 0
            && Measures'[Work Total] <= 10, "#09D543",
        -- Green
        Team = "West"
            && Measures'[Work Total] > 10
            && Measures'[Work Total] <= 20, "#FFA500",
        -- Orange
        Team = "West"
            && Measures'[Work Total] > 20, "#FF0000",
        -- Red
        Team = "East"
            && Measures'[Work Total] >= 0
            && Measures'[Work Total] <= 10, "#09D543",
        -- Green
        Team = "East"
            && Measures'[Work Total] > 10
            && Measures'[Work Total] <= 20, "#FFA500",
        -- Orange
        Team = "East"
            && Measures'[Work Total] > 20, "#FF0000",
        -- Red
        Team = "Central"
            && Measures'[Work Total] >= 0
            && Measures'[Work Total] <= 10, "#09D543",
        -- Green
        Team = "Central"
            && Measures'[Work Total] > 10
            && Measures'[Work Total] <= 20, "#FFA500",
        -- Orange
        Team = "Central"
            && Measures'[Work Total] > 20, "#FF0000",
        -- Red
        BLANK () -- Default case
    )

For a table that includes the following information

Team	Week 32	Week 33
North	5	9
East	21	34
South	9	12
West	35	41
Central	200	150

I have also tried using if statements in my measure. The following below seems to work in general, but it does not seem to filter once I include the team logic (&& Team[Name]) = "South").

Who can help me out here? Thanks in advance!

[parry2k · Accepted solution]

@dataforlife I'm confident that is a data quality issue. Please perform trim and clean steps in PQ and then check. If it still doesn't work, please share a sample pbix file, and remove sensitive information before sharing.

[dataforlife]

You're right, I tried my dax function in a test table and it works. I'll have to see what goes wrong in the column "team'' and I'll try the clean and trim.

[parry2k]

@dataforlife one another reason could be that team name has some non printable characters that are not visible and the comparison is not happening. Just guessing, may be clean and trim the team name column data in PQ to clear all spaces and non printable characters. Again this is just a wild guess.

[parry2k]

@dataforlife if you put the measure in a table visual with team[name] column, do you see "green" or "Red" color. 

[dataforlife]

No, it does not color any of the fields. I tried 

Measure Teamcolor = if(SELECTEDVALUE(Team[Name]) = "South", "Green", "Red")

just to check whether it will color any of the rows that belong to Team south, but it does not do it.

[chonchar]

@dataforlife 

Hello. I am new here and by no means an expert. But you can conditionally format values following the prompts in the screen shot below. Right click the measure from the matrix> conditional formatting>background color> and edit the rules. You can enter <,>,=, is, start with, etc as the rule and edit the hex code for each value. 

Hope this helps. 

[parry2k]

@dataforlife it should work, just to make sure, the column for Team you are using on the rows is the one you are checking in the measure, I'm sure that is the case but just double checking.

[dataforlife]

I tried debugging whether it is able to find the team and color, but it does not seem to work. It simply does not do anything. Do you have an idea why? The dax function does not color any field.

Example code:

Measure Teamcolor = if(SELECTEDVALUE(Team[Name]) = "South", "Green", "Red")