Ignore filter on my measure

[BiCp81]

Hello, I’m using a Sparkline generated via an API, and it’s working perfectly. Each dot corresponds to a week, allowing me to see the variation over time from the start of my dataset.

However, I’ve encountered a challenge: I want to filter my page data (e.g., using a slicer) to focus on specific weeks, such as Week 31 of the year. This should dynamically update all visuals on the page. However, I also want the Sparkline visual to always show the total data (ignoring any filters) to provide a full historical view.

I can’t disable the interaction between the filter and the Sparkline visual because I’m also using a card and a main measure on the page. These elements need to reflect the filtered data based on the selected date range.

Here’s the current code for my Sparkline:

Spoiler

Entrées DSIJ courbe =

-- Définir les couleurs pour la ligne et les points du graphique

VAR LineColour = "#BE2A2A" -- Couleur principale de la ligne (corrigé pour éviter l'encodage en %23)

VAR PointColour = "white"  -- Couleur des points finaux sur la courbe

-- Définir le dégradé utilisé pour remplir la zone sous la courbe

VAR Defs = "<defs>

    <linearGradient id='grad' x1='0' y1='25' x2='0' y2='50' gradientUnits='userSpaceOnUse'>

        <stop stop-color='#283896' offset='0' />

        <stop stop-color='#283896' offset='0.3' />

        <stop stop-color='white' offset='1' />

    </linearGradient>

</defs>"

-- Déterminer les limites des semaines (axe X)

VAR XMinDate = MIN(DSIJ[WeekNumber]) -- Semaine la plus ancienne

VAR XMaxDate = MAX(DSIJ[WeekNumber]) -- Semaine la plus récente

-- Déterminer les limites des valeurs (axe Y)

VAR YMinValue = MINX(VALUES(DSIJ[WeekNumber]), CALCULATE([DSIJ NB entrées])) -- Valeur minimale

VAR YMaxValue = MAXX(VALUES(DSIJ[WeekNumber]), CALCULATE([DSIJ NB entrées])) -- Valeur maximale

-- Amplification des valeurs pour rendre les variations plus visibles

VAR AmplifiedYMinValue = YMinValue * 100 -- Amplifie la valeur minimale

VAR AmplifiedYMaxValue = YMaxValue * 100 -- Amplifie la valeur maximale

VAR AmplifiedYRange = IF(AmplifiedYMaxValue - AmplifiedYMinValue = 0, 1, AmplifiedYMaxValue - AmplifiedYMinValue)

-- Si la plage amplifiée est nulle (valeurs constantes), on la remplace par 1 pour éviter une division par zéro

-- Définir les dimensions du graphique SVG

VAR ViewBoxWidth = 600  -- Largeur fixe du graphique

VAR ViewBoxHeight = 300 -- Hauteur fixe du graphique

-- Générer une table contenant les coordonnées X et Y normalisées pour chaque semaine

VAR SparklineTable = ADDCOLUMNS(

    SUMMARIZE('DSIJ', DSIJ[WeekNumber]), -- Une ligne par semaine

        "X", INT(ViewBoxWidth * DIVIDE(DSIJ[WeekNumber] - XMinDate, XMaxDate - XMinDate)), -- Normalisation en X

        "Y", INT(ViewBoxHeight * DIVIDE(([DSIJ NB entrées] * 100) - AmplifiedYMinValue, AmplifiedYRange)) -- Normalisation en Y

)

-- Construire la chaîne des points pour le tracé principal de la courbe

VAR Lines = CONCATENATEX(SparklineTable, [X] & "," & (ViewBoxHeight - [Y]), " ", DSIJ[WeekNumber])

-- Récupérer les coordonnées du dernier point de la courbe

VAR LastSparkYValue = MAXX(FILTER(SparklineTable, DSIJ[WeekNumber] = XMaxDate), [Y]) -- Y du dernier point

VAR LastSparkXValue = MAXX(FILTER(SparklineTable, DSIJ[WeekNumber] = XMaxDate), [X]) -- X du dernier point

-- Générer l'URL de l'image SVG

VAR SVGImageURL =

    "data&colon;image/svg+xml;utf8," &

    "<svg xmlns='http://www.w3.org/2000/svg' x='0px' y='0px' viewBox='0 0 " & ViewBoxWidth & " " & ViewBoxHeight & "'>" & Defs &

     -- Tracé de la zone sous la courbe

     "<polyline fill='url(#grad)' fill-opacity='0.3' stroke='transparent'

      stroke-width='0' points=' 0 " & (ViewBoxHeight / 2) & " " & Lines &

      " " & ViewBoxWidth & " " & ViewBoxHeight & " Z '/>" &

     -- Tracé de la ligne principale

     "<polyline fill='transparent' stroke='" & LineColour & "'

        stroke-linecap='round' stroke-linejoin='round'

        stroke-width='3' points=' " & Lines &

      " '/>" &

     -- Ajout d'un point pour marquer la dernière valeur

     "<circle cx='"& LastSparkXValue & "' cy='" & ViewBoxHeight - LastSparkYValue & "' r='4' stroke='" & LineColour & "' stroke-width='3' fill='" & PointColour & "' />" &

    "</svg>"

-- Retourner l'URL finale de l'image SVG

RETURN SVGImageURL

As an example, here's how it looks like when no filter is applied :

And here is how it looks like when I filter on 3 weeks :

The data change to 5484 which is great but my sparkline also changes as well, I would like it to stay unfiltered.

Thank you in advance.

[pcoley]

@BiCp81 
I suggest you to build a disconected table with all dates from the date table of your model (newdates=all(ModelDates[dates])), and use it as the x-axis field on the chart that you want without filter.
adjust the measure for that chart with:
newmeasure= CALCULATE( [measure], KEEPFILTERS(TREATAS(newdates[date],modelDate[date])),ALL(Date))
please change the names of the measure and tables as needed.
I hope this helps. if so please accept as a solution. kudos are welcome.

[BiCp81]

Hello,

Thank you very much for your answer—it helped me realize that I was focusing on the wrong problem. Your method works perfectly when selecting a week number as you demonstrated. I really appreciate it!

However, the issue arises because I've implemented a custom "week" selector to quickly view data for the current week, the previous week, two weeks ago, etc., up to five weeks prior. This selector simplifies selecting recent weeks for analysis. Here's the DAX code I used to create the selector:

Spoiler

Sélection de semaine passés =

VAR DateAujourdHui = TODAY()

VAR JourDeSemaine = WEEKDAY(DateAujourdHui, 2)  -- 2 signifie que la semaine commence le lundi, donc lundi = 1, mardi = 2, etc.

VAR DebutSemaineActuelle = DateAujourdHui - JourDeSemaine + 1  -- Début de la semaine actuelle

VAR DebutSemaineDerniere = DebutSemaineActuelle - 7  -- Début de la semaine dernière (W-1)

VAR FinSemaineDerniere = DebutSemaineActuelle - 1  -- Fin de la semaine dernière (W-1)

VAR DebutDeuxSemaines = DebutSemaineDerniere - 7  -- Début de W-2

VAR FinDeuxSemaines = DebutSemaineDerniere - 1  -- Fin de W-2

VAR DebutTroisSemaines = DebutDeuxSemaines - 7  -- Début de W-3

VAR FinTroisSemaines = DebutDeuxSemaines - 1  -- Fin de W-3

VAR DebutQuatreSemaines = DebutTroisSemaines - 7  -- Début de W-4

VAR FinQuatreSemaines = DebutTroisSemaines - 1  -- Fin de W-4

RETURN

SWITCH(

    TRUE(),

    Calendrier[Date] >= DebutSemaineActuelle && Calendrier[Date] <= DateAujourdHui, "S",

    Calendrier[Date] >= DebutSemaineDerniere && Calendrier[Date] <= FinSemaineDerniere, "S-1",

    Calendrier[Date] >= DebutDeuxSemaines && Calendrier[Date] <= FinDeuxSemaines, "S-2",

    Calendrier[Date] >= DebutTroisSemaines && Calendrier[Date] <= FinTroisSemaines, "S-3",

    Calendrier[Date] >= DebutQuatreSemaines && Calendrier[Date] <= FinQuatreSemaines, "S-4",

    "Autre"

)

This DAX formula produces a selector that allows me to choose one of the last five weeks (S, S-1, S-2, etc.). For example, if I select S-2, it filters data for the seven days from December 16th to December 22nd.

Here’s where the issue arises: when I click on a week in this custom selector, the sparkline updates. However, the implementation you provided filters based on the week number (WEEKNUM), not my custom week selector. This causes inconsistencies, as the sparkline is not aligning with the custom week filter logic.

I suspect the issue lies in how the filter is applied in the sparkline calculation. I’m unsure how to adapt the sparkline implementation to work seamlessly with my week selector without breaking it.

I want my SparkLine to be on my weeknumber data, yet to ignore my week selector I've made.

Thank you again for your help and your fast response! I wish you a great evening.

[ValtteriN]

Hi,

Here is modified dax:

Entrées DSIJ courbe =

-- Définir les couleurs pour la ligne et les points du graphique

VAR LineColour = "#BE2A2A" -- Couleur principale de la ligne

VAR PointColour = "white"  -- Couleur des points finaux sur la courbe

-- Définir le dégradé utilisé pour remplir la zone sous la courbe

VAR Defs = "<defs>

    <linearGradient id='grad' x1='0' y1='0' x2='0' y2='1' gradientUnits='objectBoundingBox'>

        <stop offset='0%' stop-color='#283896' />

        <stop offset='30%' stop-color='#283896' />

        <stop offset='100%' stop-color='white' />

    </linearGradient>

</defs>"

-- Déterminer les limites des semaines (axe X)

VAR XMinDate = CALCULATE(MIN(DSIJ[WeekNumber]),ALL(DSIJ[WeekNumber])) -- Semaine la plus ancienne

VAR XMaxDate = CALCULATE(MAX(DSIJ[WeekNumber]),ALL(DSIJ[WeekNumber])) -- Semaine la plus récente

-- Déterminer les limites des valeurs (axe Y)

VAR YMinValue = MINX(all(DSIJ[WeekNumber]), [DSIJ NB entrées]) -- Valeur minimale

VAR YMaxValue = MAXX(all(DSIJ[WeekNumber]), [DSIJ NB entrées]) -- Valeur maximale

-- Amplification des valeurs pour rendre les variations plus visibles

VAR AmplifiedYMinValue = YMinValue * 100

VAR AmplifiedYMaxValue = YMaxValue * 100

VAR AmplifiedYRange = IF(AmplifiedYMaxValue - AmplifiedYMinValue = 0, 1, AmplifiedYMaxValue - AmplifiedYMinValue)

-- Définir les dimensions du graphique SVG

VAR ViewBoxWidth = 600

VAR ViewBoxHeight = 300

-- Générer une table contenant les coordonnées X et Y normalisées pour chaque semaine

VAR SparklineTable = ADDCOLUMNS(

    SUMMARIZE(ALL(DSIJ), DSIJ[WeekNumber]), -- Une ligne par semaine

    "X", INT(ViewBoxWidth * DIVIDE(DSIJ[WeekNumber] - XMinDate, XMaxDate - XMinDate)),

    "Y", INT(ViewBoxHeight * DIVIDE(([DSIJ NB entrées] * 100) - AmplifiedYMinValue, AmplifiedYRange))

)

-- Construire la chaîne des points pour le tracé principal de la courbe

VAR Lines = CONCATENATEX(SparklineTable, [X] & "," & (ViewBoxHeight - [Y]), " ", DSIJ[WeekNumber])

-- Récupérer les coordonnées du dernier point de la courbe

VAR LastSparkYValue = MAXX(FILTER(SparklineTable, DSIJ[WeekNumber] = XMaxDate), [Y])

VAR LastSparkXValue = MAXX(FILTER(SparklineTable, DSIJ[WeekNumber] = XMaxDate), [X])

-- Générer l'URL de l'image SVG

VAR SVGImageURL =

    "data&colon;image/svg+xml;utf8," &

    "<svg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 " & ViewBoxWidth & " " & ViewBoxHeight & "'>" &

        Defs &

        "<polyline fill='url(#grad)' fill-opacity='0.3' stroke='none' points='0 " & ViewBoxHeight & " " & Lines & " " & ViewBoxWidth & " " & ViewBoxHeight & " Z' />" &

        "<polyline fill='none' stroke='" & LineColour & "' stroke-linecap='round' stroke-linejoin='round' stroke-width='3' points='" & Lines & "' />" &

        "<circle cx='" & LastSparkXValue & "' cy='" & (ViewBoxHeight - LastSparkYValue) & "' r='4' stroke='" & LineColour & "' stroke-width='2' fill='" & PointColour & "' />" &

    "</svg>"

-- Retourner l'URL finale de l'image SVG

RETURN

SVGImageURL


Test:
With weeknumber filter:

The sparkline ignores filters while the measure changes.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/