Forum Discussion

wujunmin's avatar
wujunmin
Advocate III
4 years ago

Add conditional formatting to sparkline by DAX

Measure1(mark it as image URL):

Sparkline = 
VAR MaxValue =
    MAXX ( VALUES ( 'table'[month]), [KPI])
VAR MinValue=
    MINX ( VALUES ( 'table'[month]), [KPI])
VAR Max_Width=
    IF(MinValue<0,IF(MaxValue<0, ABS(MinValue),MaxValue-MinValue),MaxValue)
VAR BarTable=
    ADDCOLUMNS(
        SUMMARIZE('table','table'[year],'table'[month],
            "Color",IF([KPI]=MaxValue,"Green",IF([KPI]=MinValue,"Red","Grey"))),
        "Rect", "<rect x='"&IF(MinValue>0,0, IF(MaxValue>0, IF([KPI]<0,120-120*(MaxValue-[KPI])/Max_Width,120*ABS(MinValue)/Max_Width),120-120*ABS([KPI])/Max_Width)) &"' y='" & ( [month] - 1 ) * 10 & "' width='" & 120*ABS([KPI]) / Max_Width & "' height='9' fill='" & [Color] & " '/>")
Return
    "data&colon;image/svg+xml;utf8,"&"
    <svg xmlns='http://www.w3.org/2000/svg' width='120' height='120' > <g transform='rotate(-90,60,60)'>"&
    CONCATENATEX(BarTable,[Rect])&"</g>
    </svg> "

 

SVG

3 Replies

  • GoofyCraig's avatar
    GoofyCraig
    New Member
    3 years ago

    I love your solution to conditonal formatting using DAX as it show min, maxc and a zero line. I was having a challenge getting your code to work...let me know if you would be able to help me troubleshoot.

  • mhonig's avatar
    mhonig
    Regular Visitor
    3 years ago

    Hello,

    I'm a Power BI user who (unfortunately) doesn't know DAX. I need to conditionally format the columns on a sparkline bar graph. I was very happy to find your post on this topic and have tried to adapt your code to my use case...without success.

     

    I have a sample .pbix file that illustrates my use case, but don't see any way to attach it to this message, so I've put some sample data in the below table and will try to describe what I need to do.

     

    I need to create a table showing, by UNIQUE_ID, the Average of PCT_CHANGE_1WEEK by DATE in a sparkline bar graph...with a slicer allowing the user to choose multiple DATEs to present - that I can do. What I'd like to do is have the bars be green for positive values and red for negative values - this is what I haven't been able to figure out.

     

    Any help would be greatly appreciated.

     

    Thank you,
    Marc

     

    DATEUNIQUE_IDPCT_CHANGE_1WEEK
    06/08/2023A-0.349
    06/01/2023A-6.181
    05/25/2023A0
    06/08/2023B0.56
    06/01/2023B-0.11
    05/25/2023B0
    06/08/2023C1.493
    06/01/2023C-1.78
    05/25/2023C0

     

     

  • phollihn's avatar
    phollihn
    Regular Visitor
    2 years ago

    Hi, Can you post the SVG measure code for the lolipop sparkline in Dax 2 above?  Thanks