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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vthree
Frequent Visitor

How to exclude Sparkline Column in Matrix View from the applied filter ?

Hi All,

      I am have a requirement to show multiple column as tooltip for which I have created a page as tooltip and used Matrix view to create the table like view and used 5 fields (metric) from data as Customer Name, Value, Score, Target Value, Score Trend.                          In order to create Score Trend, I used the Score field and then convert it to "Add to Sparkline" where I took Average of Score along with Date field from table  and  it shows Score Trend Line in the matrix and at the end I renamed "Score" to "Score Trend". 

   As business want to see only latest date data, therefore created New Column using DAX function:                                                                  Max_Date = IF (MAX(‘Table’[Date]) = ‘Table’[Date], 1,0)   

and  drop on Filter under "Filters on this page" and selected '1' in order to show the latest date details for fields Customer Name,  Value,  Score, Target Value.  As filter is getting applied in all the 5 columns but I want to exclude "Score Trend" column so that I can see the score trend line as when MAX_DATE is applied as filter (Filters on this Visual or Filters on this page ) is changes the Score Trend column into Dot(.) instead of trend line.   

 

Before applying MAX_DATE on Filter

Vthree_1-1679322713137.png

After applying MAX_DATE on Filter , it shows Dot(.) but client want to see the Trend line insted on dot(.)

Vthree_0-1679322574137.png

 

  

3 REPLIES 3
Vthree
Frequent Visitor

@lbendlin Thank you for your reply.                                                                                                                                I Created Sparkline using SVG with dummy data but not able exclude the Sparkline Column from the filter been applied as for reference I have attached the pbix file with "Linewithout Filter" & "Tooltip with Filter" page where I have applied the filter MAX_DATE on Tooltip sheet and  filter get applied on Sparkline Line column. As my requirement is to exclude the Sparkline from the applied filter. You can check the SVG code as well for Sparkline Line measure created.

Sparkline Line = 
// Static line color
VAR LineColor = "#01B8AA"
// "Date" field used in this example along the X axis
VAR XMinDate = MIN('Table'[Date])
VAR XMaxDate = MAX('Table'[Date])
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
VAR YMaxValue = MAXX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(
    SUMMARIZE('Table','Table'[Date]),
        "X",INT(100 * DIVIDE('Table'[Date] - XMinDate, XMaxDate - XMinDate)),
        "Y",INT(100 * DIVIDE([Measure Value] - YMinValue,YMaxValue - YMinValue)))
// Concatenate X & Y coordinates to build the sparkline
VAR Lines = CONCATENATEX(SparklineTable,[X] & "," & 100-[Y]," ", [Date])
// Add to SVG, and verify Data Category is set to Image URL for this measure
VAR SVGImageURL = IF(HASONEVALUE('Table'[Category]),
    "data:image/svg+xml;utf8," & 
    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 100 100'>" &
     "<polyline fill='none' stroke='" & LineColor & 
     "' stroke-width='3' points='" & Lines & 
     "'/></svg>",
     BLANK())
RETURN SVGImageURL

 

MAX_DATE = IF(MAX('Table'[Date])='Table'[Date], 1,0)
 
As, I don't find option to attach the pbix file, therefore just share the screenshot before & after filter applied.
 
When MAX_DATE Filter not applied:
Vthree_0-1679454967308.png

 

When MAX_DATE Filter applied:

Vthree_1-1679455053620.png

 

Thank you !!

 

which filters are you trying to ignore? your intermediate variables 

// "Date" field used in this example along the X axis
VAR XMinDate = MIN('Table'[Date])
VAR XMaxDate = MAX('Table'[Date])
// Obtain overall min and overall max measure values when evaluated for each date
VAR YMinValue = MINX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
VAR YMaxValue = MAXX(VALUES('Table'[Date]),CALCULATE([Measure Value]))
// Build table of X & Y coordinates and fit to 100 x 100 viewbox
VAR SparklineTable = ADDCOLUMNS(

need to be modified with REMOVEFILTERS accordingly.

lbendlin
Super User
Super User

Use SVG to handcraft your own sparklines.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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