Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
kivanct
Helper I
Helper I

Applying Color to the Closest Value

kivanct_0-1671622309767.png
Hi everyone, I would like to share my issue. I would be grateful if you could help me to solve it. 

These are the production lines in the first column like 1924-1925 etc. 

My aim is "7". 
For example , I would like to see the value closest to 7 colored by green but must be less then 7. 

RULE:
IF there is a value less than 7 in the row, make green the value closest to 7.
IF all the values in the row greater than seven, then make smallest value green.

Row 1924 = 6,75 -green
Row 1925 = there is not less value than 7, so it should select 7.5- green ( Least value)
Row 1926 = there is not less value than 7, so it should select 12,17 - green ( Least value)
Row 1927= 1,77 - green

I would like to apply the same rule for each row. 

Could you help me to create the formula ?

Regards

1 ACCEPTED SOLUTION

Hi @kivanct ,

 

Please check this modified measure.

Closet Value =
VAR _1 =
    SUM ( 'Table'[14 Saat] )
VAR _2 =
    SUM ( 'Table'[17.25 Saat] )
VAR _3 =
    SUM ( 'Table'[20.5 Saat] )
VAR _A1 =
    ABS ( _1 - 7 )
VAR _A2 =
    ABS ( _2 - 7 )
VAR _A3 =
    ABS ( _3 - 7 )
RETURN
    IF (
        _1 > 7
            && _2 > 7
            && _3 < 7,
        _3,
        IF (
            _1 > 7
                && _2 < 7
                && _3 > 7,
            _2,
            IF (
                _1 < 7
                    && _2 > 7
                    && _3 > 7,
                _1,
                IF (
                    _1 < 7
                        && _2 < 7
                        && _3 > 7,
                    IF ( _A1 >= _A2, _2, _1 ),
                    IF (
                        _1 < 7
                            && _2 > 7
                            && _3 < 7,
                        IF ( _A1 >= _A3, _3, _1 ),
                        IF (
                            _1 > 7
                                && _2 < 7
                                && _3 < 7,
                            IF ( _A2 >= _A3, _3, _2 ),
                            IF ( _A1 >= _A2, IF ( _A2 >= _A3, _3, _2 ), _1 )
                        )
                    )
                )
            )
        )
    )

vstephenmsft_0-1672024098899.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Ahmedx
Super User
Super User

one more option
https://dropmefiles.com/inzMp
-----
CF_Value =
VAR _m1 =
CALCULATE ( MAX ( Table1[14 Saat] ) )
VAR _m2 =
CALCULATE ( MAX ( Table1[17.25 Saat] ) )
VAR _m3 =
CALCULATE ( MAX ( Table1[20.5 Saat] ) )
VAR _t1 =
IF ( _m1 > 7, 1, 0 )
VAR _t2 =
IF ( _m2 > 7, 1, 0 )
VAR _t3 =
IF ( _m3 > 7, 1, 0 )
VAR _Results = _t1 + _t2 + _t3
RETURN
IF (
_Results >= 2,
MIN ( MIN ( _m1, _m2 ), _m3 ),
IF (
_Results = 0,
MAX ( MAX ( _m1, _m2 ), _m3 ),
IF (
_Results = 1,
IF ( _t1 = 1, MAX ( _m2, _m3 ), IF ( _t3 = 1, MAX ( _m1, _m2 ), BLANK () ) )
)
)
)

Screen Capture #187.png

v-stephen-msft
Community Support
Community Support

Hi @kivanct ,

 

Here's my solution.

1.Create a measure to return the colest values.

Closet Value = 
var _1=SUM('Table'[14 Saat])
var _2=SUM('Table'[17.25 Saat])
var _3=SUM('Table'[20.5 Saat])
var _A1=ABS(SUM('Table'[14 Saat])-7)
var _A2=ABS(SUM('Table'[17.25 Saat])-7)
var _A3=ABS(SUM('Table'[20.5 Saat])-7)
return IF(_A1>=_A2,IF(_A2>=_A3,_3,_2),_1)

vstephenmsft_0-1671676807400.png

 

2.Create three measures to return color results for different columns.

14Color = IF(SUM('Table'[14 Saat])=[Closet Value],"Green")
17.25Color = IF(SUM('Table'[17.25 Saat])=[Closet Value],"Green")
20.5Color = IF(SUM('Table'[20.5 Saat])=[Closet Value],"Green")

 

3.Use 'Conditional formatting' for each of three columns.

vstephenmsft_1-1671676913102.png

vstephenmsft_2-1671676977142.png

vstephenmsft_3-1671676983305.png

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thanks a lot. It worked ! But I just need a small adjustment.

For example; 

In row 1929, closest value shown as 7.39 but it should take "6" because 7,39 is greater than 7. 
And 7.39 colored by green but 6 should be colored. 

How can i adjust that ?

 

kivanct_0-1671690899115.png

kivanct_0-1671693356269.png

 

Hi @kivanct ,

 

Please check this modified measure.

Closet Value =
VAR _1 =
    SUM ( 'Table'[14 Saat] )
VAR _2 =
    SUM ( 'Table'[17.25 Saat] )
VAR _3 =
    SUM ( 'Table'[20.5 Saat] )
VAR _A1 =
    ABS ( _1 - 7 )
VAR _A2 =
    ABS ( _2 - 7 )
VAR _A3 =
    ABS ( _3 - 7 )
RETURN
    IF (
        _1 > 7
            && _2 > 7
            && _3 < 7,
        _3,
        IF (
            _1 > 7
                && _2 < 7
                && _3 > 7,
            _2,
            IF (
                _1 < 7
                    && _2 > 7
                    && _3 > 7,
                _1,
                IF (
                    _1 < 7
                        && _2 < 7
                        && _3 > 7,
                    IF ( _A1 >= _A2, _2, _1 ),
                    IF (
                        _1 < 7
                            && _2 > 7
                            && _3 < 7,
                        IF ( _A1 >= _A3, _3, _1 ),
                        IF (
                            _1 > 7
                                && _2 < 7
                                && _3 < 7,
                            IF ( _A2 >= _A3, _3, _2 ),
                            IF ( _A1 >= _A2, IF ( _A2 >= _A3, _3, _2 ), _1 )
                        )
                    )
                )
            )
        )
    )

vstephenmsft_0-1672024098899.png

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Worked ! Thanks a lot for your help.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.