Color the measure with condition

[vnqt]

Hi,

I have 2 tables : 

 Name

Name	Model	Type
Name 1	Mono	LR
Name 2	Mono	FA
Name 3	Mono	LR
Name 4	Mono	FA
Name 5	Mono	LR
Name 6	Mono	FA
Name 7	Mono	LR
Name 8	Mono	WI
Name 9	Bisite	WI
Name 10	Bisite	WI
Name 11	Bisite	WI
Name 12	Bisite	WI
Name 13	Bisite	LR
Name 14	Bisite	FA
Name 15	Bisite	LR
Name 16	Bisite	FA

Number 

Name	USED	FREE
Name 1	80	86
Name 10	90	95
Name 11	50	95
Name 12	50	95
Name 13	40	40
Name 14	40	40
Name 15	40	40
Name 16	40	40
Name 2	90	85
Name 3	55	55
Name 4	85	100
Name 5	50	95
Name 6	50	95
Name 7	50	95
Name 8	50	95
Name 9	90	95

The 2 tables have 1 - 1 relation

I have a measure to count name 

Measure = var _total = CALCULATE(DISTINCTCOUNT(Name[Name]), Name[Model] = "Mono" &&  (Name[Type] = "WI" || Name[Type] = "LR"))

var _cf = CALCULATE(DISTINCTCOUNT((Name[Name]), Name[Model] = "Mono" && (Name[Type] = "WI" || Name[Type] = "LR" ), FILTER(Number,  Number[USED]<= 85 ) , FILTER(Number[FREE] <=85), USERELATIONSHIP( Number[Name],  (Name[Name] )))

RETURN _cf &" / "&_total

RESULT : 1/5   

Name 3  / Name 1, Name 3, Name 5, Name 7, Name 8

Name	USED	FREE
Name 1	80	86
Name 3	55	55
Name 5	50	95
Name 7	50	95
Name 8	50	95

There are 4 differences, IF one of these 4 Names having: 

 -  85 <MAX (USED or FREE)   < = 90    THEN  yellow

 - 90< MAX(USED or FREE) <= 95   THEN  orange

-  MAX(USED or FREE) > 95    THEN  red

In this case, the color of the measure should be orange (90< MAX(USED or FREE) <= 95)

Thank you in advance for your help.

Tg 

[manikumar34]

@vnqt 
color measure given by dane does work. What are you exactly looking for?

[vnqt]

Hi, 

I created the measure color of @danextian but it doesn't work. 

I would like to create a measure to color the card : 

RESULT : 1/5   

Name 3  / Name 1, Name 3, Name 5, Name 7, Name 8

Name	USED	FREE
Name 1	80	86
Name 3	55	55
Name 5	50	95
Name 7	50	95
Name 8	50	95

There are 4 differences, IF one of these 4 Names having: 

 -  85 <MAX (USED or FREE)   < = 90    THEN  yellow

 - 90< MAX(USED or FREE) <= 95   THEN  orange

-  MAX(USED or FREE) > 95    THEN  red

In this case, the color of the measure should be orange (90< MAX(USED or FREE) <= 95)

Thank you 

[manikumar34]

@vnqt ,
I can see it is working, could you please share what is your exact requirement?

[vnqt]

Hi, 

I would like to color the card with the color 

And the rule of color is: 

The card is orange because the value USED or FREE of the following name (Name 1,Name 5, Name 7, Name 8  )   > 90 AND <=95

 -  85 <MAX (USED or FREE)   < = 90    THEN  yellow

 - 90< MAX(USED or FREE) <= 95   THEN  orange

-  MAX(USED or FREE) > 95    THEN  red

Thank you

[manikumar34 · Accepted solution]

@vnqt , 
Understood, use the below measure to do that. 

color1 =

VAr val=

CALCULATE(MAX(MAX(Number[USED]),MAX(Number[FREE])),'Name'[Model] = "Mono" &&  ('Name'[Type] = "WI" || 'Name'[Type] = "LR"))

RETURN

val

[vnqt]

Thank you for your help . 

[danextian]

Hi @vnqt 

Try this:

color =
VAR _used =
    CALCULATE (
        SELECTEDVALUE ( Number[USED] ),
        USERELATIONSHIP ( 'Name'[Name], 'Name'[Name] )
    )
VAR _free =
    CALCULATE (
        SELECTEDVALUE ( Number[FREE] ),
        USERELATIONSHIP ( 'Name'[Name], 'Name'[Name] )
    )
VAR _max =
    MAX ( _used, _free )
RETURN
    SWITCH ( TRUE (), _max > 95, "red", _max > 90, "orange", _max > 85, "yellow" )

[vnqt]

Hi, sorry I didn't give enough info in the first post, I have added some more info. thank you for your help

[vojtechsima]

@vnqt 

try something simialr to this (create measure for USED/FREE or use MAX etc, depends how yo will display in visual):

SWITCH(TRUE(),
[USED] || [FREE] > 85, "yellow",
[USED] || [FREE] > 90, "orange",
[USED] || [FREE] > 95, "red"
)

[vnqt]

Hi, I have justed added some more info, hope you could help. Thank you.