Forum Discussion

JK14's avatar
JK14
New Member
2 years ago
Solved

DAX Highlight Range Selection

Hi All

I would love to have some helping hands with a PowerBI DAX formula to multi-select highlight range results from filter. At current I can get DAX to highlight 1 set of selection from filter, however if I select more than 1 it will highlight all values between Max and Min and not as ones specifically selected.
For example, if selection range was both 0-49 and 150-199 at the moment report will highlight all results from 0-199.


I have a table with range

 
OrderMinValueMaxValueRange
10490-49
2509950-99
3100149100-149
4150199150-199
5200249200-249
6250299250-299

 

Background colour condition based off below condition being 1:

 

Result =
VAR ComMin = [CommittedMin]
VAR ComMax = [CommittedMax]
VAR ComValue = [Committed]
RETURN
IF(ISFILTERED('Table'[Range]), IF(NOT(ISBLANK(ComValue))&&ComValue >= ComMin && ComValue <= ComMax, 1, 0), 0)

 

And a few more measures:

 

CommittedMin = DIVIDE(MINX(VALUES(' Difference'[MinValue]), [MinValue]), 100)
CommittedMax = DIVIDE(MAXX(VALUES(Difference'[MaxValue]), [MaxValue]), 100)
Committed = IF([Check] <> 0, BLANK(), DIVIDE([Billed], [Planned], 0))

 

 

Anyone who can shed some light will be greatly appreciated, thank you.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi, 
    Please check the below picture and the attached pbix file.

     

     

    background color condition: =
VAR _t =
    FILTER (
        ADDCOLUMNS (
            'Range',
            "@condition",
                IF (
                    [Percentage:] >= 'Range'[MinValue]
                        && [Percentage:] <= 'Range'[MaxValue],
                    1
                )
        ),
        [@condition] = 1
    )
RETURN
    IF ( ISFILTERED ( 'Range'[Range] ), IF ( COUNTROWS ( _t ) > 0, 1, 0 ) )

     

2 Replies

  • JK14's avatar
    JK14
    New Member
    2 years ago

    Thanks you very much Jihwan_Kim!   This works perfectly and I never would have thought of ADDCOLUMNS()!

    Much appreciated your help ğŸ˜€

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi, 
    Please check the below picture and the attached pbix file.

     

     

    background color condition: =
VAR _t =
    FILTER (
        ADDCOLUMNS (
            'Range',
            "@condition",
                IF (
                    [Percentage:] >= 'Range'[MinValue]
                        && [Percentage:] <= 'Range'[MaxValue],
                    1
                )
        ),
        [@condition] = 1
    )
RETURN
    IF ( ISFILTERED ( 'Range'[Range] ), IF ( COUNTROWS ( _t ) > 0, 1, 0 ) )