Single select slicer but visual should ignore it (partially)

[lekkerbek]

Hi everybody,

I have a slicer (single select)on my page where the user can select the desired month of the year. A card visual shows the YTD value based on that slicer selection. So for example if March were chosen it shows the revenue YTD March.

I would like to add a table that shows the revenue for each month individually. By default it only shows the month selected. If I remove the filter by going to edit interactions, it shows the values that I want, but it shows them for the entire year (or whatever the dataset has). I would like it to stop at the month selected like in the screenshot. My date table is in the other screenshot.

ChatGPT couldn't help me. Hopefully one of you can 🙂

[Ritaf1983]

Hi @lekkerbek 

Please refer to the linked guide of Goodly, he shows very easy method to achieve the same scenario 🙂

https://www.youtube.com/watch?v=xZcO83X5K30&t=2s

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

[danextian]

Hi @lekkerbek 

Filter from a related table will always show just the rows within the range selected. You need a disconnected table to achieve what you want.  Please see the attached pbix for details.

[lekkerbek]

Thanks for the demo file. I tried to replicate it, but I can't get it to work. It appears to be exactly the same as your, but in my case it still shows all months. Testfile is here: Test PBIX 

[Anonymous · Accepted solution]

Hi @lekkerbek ,

Please update the formula of measure [Test] as below, you can find the details in the attachment.

Test = 
VAR _year =
    SELECTEDVALUE ( 'dimDatum'[Year] )
VAR _month =
    SELECTEDVALUE ( 'dimDatum (los)'[MonthOfYear] )
RETURN
    SUMX (
        FILTER (
            'fctOmzet',
            YEAR ( 'fctOmzet'[Datum] ) = VALUE ( _year )
                && MONTH ( 'fctOmzet'[Datum] ) <= VALUE ( _month )
        ),
        [Omzet X]
    )

Best Regards

[lekkerbek]

That worked :-).

Thanks very much (and to all others for their suggestions also).