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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wujunmin
Advocate I
Advocate I

Add conditional formatting to sparkline by DAX

wujunmin_0-1642253530460.png

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> " 

wujunmin_1-1642253698927.png

 

3 REPLIES 3
phollihn
Regular Visitor

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

 

mhonig
Regular Visitor

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

 

 

GoofyCraig
New Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors