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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
sjl_74
Employee
Employee

Exclude all rows where the first instance of a value occurs outside a slicers date range

I have a Power Bi dashboard.

sjl_74_0-1709655442352.png

 

This dashboard references two tables…

One table is called: 'Data_Table'

'Data_Table' contains three columns: 'Data', 'Date' and 'Date_Earliest'

'Data' - Contains a string values - Values repeat multiple times.

'Date' Contains a date values.

'Date_Earliest' contains the date where the first instance of the 'Data' value was raised.

sjl_74_1-1709655472185.png

 

The second table is called: 'Slicer_Table'

'Slicer_Table' contains one column called: 'Slicer_Options'

'Slicer_Options' contains two values: 'Date' and 'Date_Earliest'

sjl_74_2-1709655490580.png

 

The dashboard has two slicers.

One is a date slicer.

This slicer uses the field 'Data_Table'->'Date'

This limited the date range of displayed data to the given date range.

sjl_74_3-1709655502029.png

 

The second is a tile slicer.

This slicer uses the field 'Slicer_Table'->'Slicer_Options'

& provides two options 'Date' or 'Date_Earliest'

This currently does nothing else.

sjl_74_4-1709655516678.png

 

I've been trying (& failing) to figure out how to setup the tile slicer so that:

If ‘Date’ is selected; excludes all rows which fall outside the slicers date range (this is the current/default behaviour)

If ‘Date_Earliest’ is selected; excludes all rows where the date of the first instance of the 'Data' is outside of the date slicer range.

 

e.g. in this table, the column 'Data' contains the value 'A' four times, with first instance appearing on the date 01/01/2024.

Should ‘Date_Earliest’ be selected in the tile slicer &  the date range slider be set to start from, 02/01/2024

Then all rows containing the value 'A' should be excluded from the results.

sjl_74_5-1709655532236.png

 

Hopefully this makes some kind of sense.

Is this possible\feasible in PowerBi?


Note: With help from this forum, I have been able to create a measure which, displays a column depending on which tile slicer value was selected. This allowed me to switch between 'Date' & 'Earliest_Date' columns. 

sjl_74_0-1709656476953.png

However, since it is a measure, I am unable to create a releationship between the measure and the 'Date' column used with the date slicer.

 

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

Hi @sjl_74 

 

Unfortunately, as far as I know the Slicer does not support the feature. The Slicer can not anti-selection the field.

Maybe you can only select the Data by hand.

While I think the feature you are trying to realize can be very useful, maybe you can submit your ideas through Microsoft Fabric Ideas. It's a place for users to share and discuss improving the product.

Here is the link of the Microsoft Fabric Ideas:

Home (microsoft.com)

 

Best Regards

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

View solution in original post

2 REPLIES 2
sjl_74
Employee
Employee

Hi @v-zhengdxu-msft 

That's a shame - Though, I do at least feel a littler better in my repeated failures 🙂

Thanks for you help!

v-zhengdxu-msft
Community Support
Community Support

Hi @sjl_74 

 

Unfortunately, as far as I know the Slicer does not support the feature. The Slicer can not anti-selection the field.

Maybe you can only select the Data by hand.

While I think the feature you are trying to realize can be very useful, maybe you can submit your ideas through Microsoft Fabric Ideas. It's a place for users to share and discuss improving the product.

Here is the link of the Microsoft Fabric Ideas:

Home (microsoft.com)

 

Best Regards

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.