Sparklines in matrix using switch

[SUMESHKUMAR22]

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 below switch measure & converting into sparklines it just shows blank (no visual rendering). Though I kept the data type in similiar format but still the line is not rendering but the data value is showing for last 7 days. 

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 Impression =
   CALCULATE(
       COUNTROWS('VBI Vaccines'),
       'VBI Vaccines'[EVENT_NAME] = "Impression_Delivered",
       Last_7_Days
   )

VAR CTR = DIVIDE(Click, Impression, 0)

RETURN
   SWITCH(
       TRUE(),
       SelectedKPI = "Impressions", FORMAT(Impression, "0.00") * 1,
       SelectedKPI = "Clicks", FORMAT(Click, "0.00") * 1,
       SelectedKPI = "CTR %", FORMAT(CTR * 100, "0.00") * 1,
       BLANK()
   )

thanks!

Snaps:

 

[v-jingzhan-msft · Accepted solution]

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]

Hi @SUMESHKUMAR22 

The result of the formula is text because you're using the FORMAT function.
Text cannot be placed on the Y-axis of a chart,
which is why it also can't be used in sparklines.
Since sparklines don't display labels anyway, it doesn't matter how the values appear, so just keep the numeric value.
Remove the formatting from the formula and try again.

If it won't work 

Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to

How to provide sample data in the Power BI Forum

If possible, upload a simplified .pbix file using this guide:

 How to upload PBI in Community

If my answer was helpful please give me a Kudos and accept as a Solution.

[SUMESHKUMAR22]

Hi @Ritaf1983 ,

Thanks for prompt response.

If you look through the measure, Im using the format function keeping all of them in whole number ('0.00' format).

 

• Also, if I remove formatting it will show visual error because all of them have different data type & in the end it has to be converted into single datatype.

Thanks!

[v-jingzhan-msft · Accepted solution]

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!

[SUMESHKUMAR22]

Hi @v-jingzhan-msft ,

Kudos:), Thanks for this catch.
Unfortunately the same DAX is not working in my original file attached hereby even though the conditions are the same. Could you please help me in trouleshooting it?

File link: Test_Original 

Thanks in advance!

[v-jingzhan-msft]

Hi @SUMESHKUMAR22 

According to my understanding of your original measure, it is calculating the past 7 days' running total for a date (including the date) by using DATESINPERIOD. So, on the sparkline, it calculates the running total for every day on the x-axis. 

Maybe you don't want the trend of past 7 days' running totals, but the trend of daily count values of the last 7 days? Can you use a normal line chart to show the results you want to achieve for Sparklines? An example of a KPI is sufficient.

From what I've learnt, if the current measure brings you the correct result and you want to display only the last 7 days' data points, a simple method is to filter Day column like below on the matrix visual. 

Besides, currently it doesn't support to highlight the data labels of data points on sparklines. 

Best Regards,
Jing

[SUMESHKUMAR22]

Thanks for addressing the problem promptly.  

Sorry! but if you look into the below measure its exactly calculating the last 7-days, infact the same calculation is used in your solution file as well , which is why the data label is coming correctly. 
Not sure then why its unable to plot last 7 days in sparkline.

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(

      COUNT('VBI Vaccines'[EVENT_NAME]),

       '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(

       COUNT('VBI Vaccines'[EVENT_NAME]),

       'VBI Vaccines'[EVENT_NAME] = "Impression_Delivered",

       Last_7_Days

   )

VAR CTR = DIVIDE(Click, Impression, 0)

RETURN

   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

   )

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

   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

   )

Thanks!

[Ritaf1983]

Hi again @SUMESHKUMAR22 

Let's clarify things:

1. The FORMAT function converts any value into a text data type.

2. There’s no such thing as a measure that returns different data types.
3. Sparklines can only display measures that return numeric values

4. As I mentioned in my previous response, it’s easier to assist with a file to understand the desired outcome, since screenshots alone don’t provide enough detail for troubleshooting.

[SUMESHKUMAR22]

Hi @Ritaf1983 ,

Thanks for correcting 🙂

Attached my sample file in the above thread for troubleshooting.

Thanks in advance.

[Ritaf1983 · Accepted solution]

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.