How to set slicer value to first available value form table in Power BI?2020-01-13 api powerbi powerbi-desktop power-bi-report-server
I have a requirement that the selected value in slicer must be valid.
Let us suppose if I select a value in Store slicer and that store gets deleted from the company. The slicer will still show its name with no data in visuals interacting with the slicer.
Default Value Selected
Updated Slicer Value Manually
But I want only the relevant store selected in my Store slicer. I know its slicer's property to retain the value which is set in it while publishing the report, but is there any workaround for it.
This is happening only because it's your default value, otherwise the slicer should lose the values automatically once its deleted from your base. If possible, change your default to reflect a store value, of which you are certain that it's always going to be there. I believe the default value will get retained even if there is no data. Another way to do this could be to default to ALL stores and then let the user select what is needed. This may not be what you are looking for, since I am not aware of your specific requirements, but hopefully it helps you arrive at a solution.
The essence of your question has been asked and covered in this thread:
The answer is NO, but I can propose a workaround.
- In the dimension table with stores, add a calculated column
Rankwhich will determine the store with the highest sales. You may use RANKX function for that.
StoreNamecalculated column which returns the text value
"The biggest store"(or
"Top 1"- or whatever) and original store names for all the other stores. Use IF.
- Put the column
"The biggest store"value to the slicer.
- Add a visual (card, table) where you will display the original name of currently selected store.
- Sort the column
StoreNameby Rank column designed for that purpose so that
The biggest storewill float up to the top position in the slicer. Here is how to sort the column by another column.
Since there is always a store with the highest sales you may always have that value ticked in the slicer and it will always show data.
In this example
"The biggest store" is "Store for girls". I keep it selected on the slicer. Then I remove all the records of that store from fact table. Apply. And the slicer is still selected as
"The biggest store" but now the biggest store means "Store for ladybirds".
Here is a sample file for download (with both approaches in M and DAX):
You can make DimStore table completely in DAX by adding calculated table:
DimStore_DAX = SUMMARIZECOLUMNS ( Sales[Store], "Sales", [Sale], "Rank", RANKX ( ALL ( Sales[Store] ), [Sale] ), "StoreName", IF ( RANKX ( ALL ( Sales[Store] ), [Sale] ) = 1, "The biggest store", VALUES ( Sales[Store] ) ) )
- how to add filter on measure in power BI?
- How to use default and available filter in power bi?
- Table Visual as per Slicer Selection
- How do you set the Content-Type header for an HttpClient request?
- Create a custom indicator using DAX on power BI
- Filtering a visual based on number of values selected in slicer
- Displaying nothing in card values until a single slicer selection is made