如何将切片器值设置为 Power BI 中的第一个可用值表单表? [英] How to set slicer value to first available value form table in Power BI?

问题描述

我要求切片器中的选定值必须有效.

让我们假设，如果我在 Store slicer 中选择一个值，并且该存储从公司中删除.在与切片器交互的视觉对象中没有数据时，切片器仍将显示其名称.

选择的默认值

手动更新切片器值

但我只想在我的商店切片器中选择相关商店.我知道它的切片器属性可以保留在发布报告时在其中设置的值，但是否有任何解决方法.

解决方案

您的问题的本质已在此线程中提出并涵盖:

这是一个可供下载的示例文件(在 M 和 DAX 中都有两种方法):

M 默认切片器值.pbix

您可以通过添加计算表完全在 DAX 中制作 DimStore 表:

DimStore_DAX =汇总列(销售[商店],"销售", [销售],"Rank", RANKX ( ALL ( Sales[Store] ), [Sale] ),"StoreName", IF ( RANKX ( ALL ( Sales[Store] ), [Sale] ) = 1, "最大的商店", VALUES ( Sales[Store] )))

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.

解决方案

The essence of your question has been asked and covered in this thread:

Initial value of Power BI slicer based on another slicer choice

The answer is NO, but I can propose a workaround.

1. In the dimension table with stores, add a calculated column Rank which will determine the store with the highest sales. You may use RANKX function for that.
2. Add StoreName calculated 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.
3. Put the column StoreName which contains "The biggest store" value to the slicer.
4. Add a visual (card, table) where you will display the original name of currently selected store.
5. Sort the column StoreName by Rank column designed for that purpose so that The biggest store will 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):

M Default Slicer Value.pbix

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] )
    )
)

这篇关于如何将切片器值设置为 Power BI 中的第一个可用值表单表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！