cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
klehar
Helper IV
Helper IV

Conditional Line chart

Hi,

 

Below is my sample dataset

DateValueFilter/Type
1/1/2021100Actuals
1/2/2021200Actuals
1/3/2021300Actuals
1/4/2021400Actuals
1/5/2021500Actuals
1/6/2021650Forecast 1
1/6/2021640

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

DateValue
1/1/2021100
1/2/2021200
1/3/2021300
1/4/2021400
1/5/2021500
1/6/2021650

 

 

Expected output : Forecast 2 as filter

DateValue
1/1/2021100
1/2/2021200
1/3/2021300
1/4/2021400
1/5/2021500
1/6/2021640

 

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

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

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:

slicer.gif

 

I've attached the sample PBIX for you

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

@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 YouTube.gif 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!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





PaulDBrown
Super User
Super User

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:

slicer.gif

 

I've attached the sample PBIX for you

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Tahnsk @PaulDBrown .

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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






klehar
Helper IV
Helper IV

@parry2k for help

vojtechsima
Super User
Super User

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.

vojtechsima_2-1646146520181.png

 



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

vojtechsima_0-1646146477587.pngvojtechsima_1-1646146486798.png

 

@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?

@Greg_Decklercan you please help

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, August 25 at 11a PDT for a great session with Ted Pattison!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors