Conditional Line chart

[klehar]

Hi,

Below is my sample dataset

Date	Value	Filter/Type
1/1/2021	100	Actuals
1/2/2021	200	Actuals
1/3/2021	300	Actuals
1/4/2021	400	Actuals
1/5/2021	500	Actuals
1/6/2021	650	Forecast 1
1/6/2021	640	Forecast 2

Requirement 1

I have a filter called Type from which I want to hide "Actuals". So it will show Forecast 1 or Forecast 2

Requirement 2

I also want a logic such that if Forecast 1 is selected in filter then the row with value Forecast 2( and all future Forecast) should be removed

If i select forecast 2 in the filter then Forecast 1 value should be filtered out.

Expected output : Forecast 1 as filter

Date	Value
1/1/2021	100
1/2/2021	200
1/3/2021	300
1/4/2021	400
1/5/2021	500
1/6/2021	650

Expected output : Forecast 2 as filter

Date	Value
1/1/2021	100
1/2/2021	200
1/3/2021	300
1/4/2021	400
1/5/2021	500
1/6/2021	640

I'll then use this Date and Value column on a line chart with the filter enabled that will filter the Forecast part of the line chart dynamically

[parry2k]

@klehar Although @PaulDBrown  has already provided an amazing solution, here is another approach to how you can solve it, and you don't need a separate slicer table. See attached. Cheers!

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[PaulDBrown · Accepted solution]

Here is a way.

1) Create a table for slicer for the forecast values using:

Forecast Slicer = 
CALCULATETABLE(VALUES('Table'[Filter/Type]),'Table'[Filter/Type] <> "Actuals")

leave this slicer table unrelated in the model

2) create the followoing measure to use in the visual

Forecast Value =
VAR F1 =
    CALCULATE ( [Sum Value], 'Table'[Filter/Type] IN { "Forecast 1", "Actuals" } )
VAR F2 =
    CALCULATE ( [Sum Value], 'Table'[Filter/Type] <> "Forecast 1" )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Forecast Slicer'[Filter/Type] ),
        "Forecast 1", F1,
        "Forecast 2", F2
    )

to get:

I've attached the sample PBIX for you

[klehar]

Tahnsk @PaulDBrown .

I wonder why a new table is required for filter. Cant we use the same column from the existing table

[PaulDBrown]

If you create a slicer from the column, you would need to select "Actuals" for the values to be included. You stated that you didn't want "actuals" to be present in the slicer

[klehar]

@parry2k for help

[vojtechsima]

Hi, @klehar,
I tried many ways how to do it, the cleanest is probably to manually filter what you want and then save it as Bookmark (create 3 Bookmarks, Forecast1+Actuals, Forecast2+Actuals, Forecast Only) and then create buttons that would switch between those 3 views.

Or you can create calculated columns in your table for all scenarios and use 3 separated slicers to filter:

[klehar]

@vojtechsima 
appreciate yor response.

However, bookmarks is not practical since in the future I'll have 10+ Forecast types

Having multiple slicers is also not practical because of same reasons

Do you have an alternative?

[klehar]

@Greg_Decklercan you please help