Power BI 动态图例 [英] Power BI Dynamic Legend

问题描述

我正在处理地理层次结构.我希望折线图或丝带图上的图例显示基于切片器选择的下一个最低相关细节级别.

I'm working with a geographical hierarchy. I'd like for the Legend on my line or ribbon chart to show the next lowest relevant level of detail based upon a slicer selection.

目标:无切片器选择会生成区域图表(东南、东北等的线).从切片器中选择东北"区域会生成州图表(缅因州、新罕布什尔州等的线条).从切片器中选择缅因州"会生成县图，依此类推.

GOAL: No Slicer Selection yields a Regional Chart (lines for Southeast, Northeast, etc). Select "Northeast" Region from Slicer yields a State Chart (lines for Maine, New Hampshire, etc.) Select "Maine" State from Slicer yields a County Chart, and so forth.

我可以在卡片中使用 HASONEVALUE 和 VALUES 的组合来获取动态单个值.但是，Ribbon 或 Line Charts 的 Legend 字段不会接受这样的度量.

I can do this in a Card using a combination of HASONEVALUE and VALUES to obtain a dynamic single value. However, the Legend field of Ribbon or Line Charts will not accept such a measure.

有没有办法根据切片器选择创建带有动态图例的 Power BI 折线图或功能区图?

Is there any way to create a Power BI Line or Ribbon chart with a dynamic Legend based upon slicer selection?

我不想为所有层次结构节点创建单独的度量，因为有数千个可能的叶子.

I'd rather not create a separate measure for all hierarchy nodes as there are thousands of possible leaves.

推荐答案

假设我有一些美国最大的 20 个县的人口数据如下:

Let's say I have some population data on the largest 20 US counties as follows:

Data = DATATABLE(
       "Rank", INTEGER, "Region", STRING, "County", STRING, "State", STRING,
       "2010", INTEGER, "2012", INTEGER, "2014", INTEGER,
       {{1,"West","Los Angeles","California",9818605,9826773,9826773},
        {2,"Midwest","Cook","Illinois",5194675,5199971,5199971},
        {3,"South","Harris","Texas",4092459,4109362,4109362},
        {4,"South","Maricopa","Arizona",3817117,3824834,3824834},
        {5,"West","San Diego","California",3095313,3105115,3105115},
        {6,"West","Orange","California",3010232,3017598,3017598},
        {7,"South","Miami-Dade","Florida",2496435,2503034,2503034},
        {8,"Northeast","Kings","New York",2504700,2508515,2508515},
        {9,"South","Dallas","Texas",2368139,2375207,2375207},
        {10,"Northeast","Queens","New York",2230722,2233895,2233895},
        {11,"West","Riverside","California",2189641,2202978,2202978},
        {12,"West","San Bernardino","California",2035210,2042027,2042027},
        {13,"West","King","Washington",1931249,1937157,1937157},
        {14,"West","Clark","Nevada",1951269,1953927,1953927},
        {15,"South","Tarrant","Texas",1809034,1816850,1816850},
        {16,"West","Santa Clara","California",1781642,1786267,1786267},
        {17,"South","Broward","Florida",1748066,1752122,1752122},
        {18,"South","Bexar","Texas",1714773,1723561,1723561},
        {19,"Midwest","Wayne","Michigan",1820584,1815246,1815246},
        {20,"Northeast","New York","New York",1585873,1587481,1587481}
       })

使用此数据作为查询编辑器中的源，我们将生成两个表.第一个将是一个 Fact 表，它对年份进行了反透视，对于前三个县来说看起来像这样:

Using this data as a source in the query editor, we will produce two tables. The first will be a Fact table that unpivots the years and looks like this for the top three counties:

第二个将是一个 Bridge 表，它只是唯一的 Region、State、County 组合的列表:

The second will be a Bridge table that's just a list of unique Region, State, County combinations:

我们将使用 DAX 创建一张决赛桌.这将是我们的 Legend 表.

We'll create one final table using DAX. This will be our Legend table.

Legend = UNION(SUMMARIZECOLUMNS(Fact[Region], Fact[Rank], "Level", 1),
             SUMMARIZECOLUMNS(Fact[State], Fact[Rank],  "Level", 2),
             SUMMARIZECOLUMNS(Fact[County], Fact[Rank],  "Level", 3)
             )

确保在 Rank 列上设置关系，以便您的关系图如下所示:

Make sure the relationships are set on the Rank columns so that your relationship diagram looks like this:

现在我们将编写一个度量来利用我们刚刚在 Legend 表中创建的这些级别.

Now we'll write a measure that takes advantage of these levels that we just created in the Legend table.

Measure = 
    VAR StateCount = CALCULATE(DISTINCTCOUNT(Bridge[State]),
                         ALLSELECTED(Bridge), ALLSELECTED(Legend))
    VAR RegionCount = CALCULATE(DISTINCTCOUNT(Bridge[Region]),
                          ALLSELECTED(Bridge), ALLSELECTED(Legend))
    VAR LevelNumber = SWITCH(TRUE(), StateCount = 1, 3, RegionCount = 1, 2, 1)
    RETURN CALCULATE(SUM(Fact[Population]), Legend[Level] = LevelNumber)

基本上，我们只想显示适当级别的人口.

Basically, we only want to show the population for the appropriate level.

在图例框中使用 Legend[Region] 和轴为 Fact[Year] 的折线图上使用此度量，当切片器为留空:

Using this measure on a line chart with Legend[Region] in the legend box and Fact[Year] on the axis, the result looks like this when slicers are left blank:

当我们选择一个地区然后选择一个州时，我们得到以下结果:

When we select a region and then a state, we get the following:

这是我为此创建的 PBIX 文件的链接:Variable Legend.pbix

Here's a link to the PBIX file I created for this: Variable Legend.pbix

这篇关于Power BI 动态图例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！