Forum Discussion

SUMESHKUMAR22's avatar
SUMESHKUMAR22
Helper IV
1 year ago
Solved

Sparklines in matrix using switch

Hi Team, Approach: created a disconnected table for kpi table & then used switch to return kpi values for last 7 days.   Problem: Im trying to add sparklines in matrix viz but when Im placing the...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi SUMESHKUMAR22 

     

    I mocked a simplified demo and made some testing. I found something interesting:

    • when the SWITCH function returns any valid number (e.g. 100 or 0) as an alternate value, the sparklines can display correctly;
    • when the SWITCH function returns blank value as the alternate value, it displays blank on all rows. 

     

    I haven't fixed why this happens. It seems there is some invisible filtering within the context that we don't know. 

     

    At the moment, at least as a workaround, you can modify the alternate value BLANK() into 0 to display the sparklines. And switch off Row subtotals in the matrix to hide the total row. You will get a result similar to below image.

    Hope this would be helpful. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi SUMESHKUMAR22 
    According to the filter the sparks show only last 7 days, you can get the desired result with 2 steps :
    1. Add the calculated column to the date table :

    last 7 days flag =
    var maxD = CALCULATE(max('Time_Period'[Day]),all('Time_Period'))
    var minD = maxD-6
    var check = if('Time_Period'[Day]>=minD && 'Time_Period'[Day]<=maxD,1,0)
    Return check

    2. Use this column in your original DAX code :

    Selected_KPI_Value_Sparkline =
    VAR SelectedKPI = SELECTEDVALUE(KPI_Table[KPI Name])
    VAR Last_7_Days = DATESINPERIOD(Time_Period[Day], MAX(Time_Period[Day]), -7, DAY)

    VAR Click =
       CALCULATE(
           COUNTROWS('VBI Vaccines'),
           'VBI Vaccines'[EVENT_NAME] = "Click_Delivered",
           Last_7_Days
       )

    VAR Conversion = COALESCE(
        CALCULATE(
            COUNT('VBI Vaccines'[EVENT_NAME]),
            'VBI Vaccines'[EVENT_NAME] IN { "click-through" , "view-through" },
            Last_7_Days
        ), 0)

    VAR Impression =
       CALCULATE(
           COUNTROWS('VBI Vaccines'),
           'VBI Vaccines'[EVENT_NAME] = "Impression_Delivered",
           Last_7_Days
       )

    VAR CTR = DIVIDE(Click, Impression, 0)

    RETURN
    if(SELECTEDVALUE(Time_Period[last 7 days flag])=1,   SWITCH(
           TRUE(),
           SelectedKPI = "Impressions", FORMAT(Impression, "0.00") * 1,
           SelectedKPI = "Clicks", FORMAT(Click, "0.00") * 1,
           SelectedKPI = "CTR %", FORMAT(CTR * 100, "0.00") * 1,
           SelectedKPI = "Conversion", FORMAT(Conversion, "0.00") * 1,
           0
       ),BLANK())

    Result :

    According to minimum/maximum labels, unfortunately, we don't have the functionality .
    But you can use some workaround to show it after the spark like I did in the attached image :

    Or use a tooltip from the measure itself :

    The pbixes are attached

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