Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
klehar
Helper V
Helper V

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
Community Champion
Community Champion

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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Community Champion
Community Champion

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 V
Helper V

@parry2k for help

vojtechsima
Memorable Member
Memorable Member

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.