Power BI-仅显示属于所需选择（和逻辑）的数据 [英] Power BI - Show only data that belongs to the required selection (And Logic)

问题描述

我有一个这样的表

 角色技能资源
数据分析师RA 
数据分析师Python A 
数据分析师SQL B 
业务分析师SQL A 
  

我的技能正在过滤。我在仪表板上有多个视觉效果。

如果我选择了SQL和Python，那么数据分析器和业务分析器的结果都将显示在视觉上。

但是，我希望它仅显示Data Analyst结果，因为只有Data Analyst才具有所有选定的技能。

要实现这一点，我想创建一个

更新：-如果在这里选择SQL，我将获得2个不同的资源卡片视觉与资源有关，但是，如果我选择SQL和Python，则会在卡片视觉上获得与资源有关的0资源和与Role度量中的角色有关的1个角色计数。

请帮助我创建该度量。

解决方案

也许有人会建议更优雅的方式；我提出了以下想法。

创建一个度量（我称您的表为数据）：

 具有所有选定的技能
 = 
 VAR 
 Selected_Skills = ALLSELECTED（Data [Skills]）
 VAR 
 Role_Skills = CALCULATETABLE（值（Data（Skills] ），ALL（Data [Skills]））
 VAR 
 Missing_Skills = COUNTROWS（EXCEPT（Selected_Skills，Role_Skills））
 RETURN 
 IF（NOT（Missing_Skills），1）
  

如果将度量放置在与角色相对的视图中，则会产生以下结果：

此代码的工作方式：

• 首先，我们存储所有se在变量 Selected_Skills中选择技能；


• 第二，我们将角色可用的所有技能存储在变量 Role_Skills中。我们必须使用ALL（Data [Skill]）来忽略技能切片器的选择；


• 第三，由于以上两个变量都是表，因此我们可以使用EXCEPT函数来查找它们之间的不同。在这里，我们告诉DAX查找Selected_Skills中的哪些记录在Role_Skills中不存在。将结果存储在变量 Missing_Skills中。


• 最后，如果Missing_Skills为零，则表示该角色具有所有选定技能，我们将其标记为1（尽管您可以使用True / False等）。

我看到的这种方法的问题是，如果技能选择器没有选择（显示所有技能），则公式可能会对所有角色返回空白，并且所有视觉效果都将为空白。从技术上讲，这是正确的-本质上说，没有一个角色拥有所有技能。但是，如果这不是您想要的行为，请考虑稍微修改一下的方法：

 缺少技能计数
 = 
 VAR 
 Selected_Skills = ALLSELECTED（Data [Skills]）
 VAR 
 Role_Skills = CALCULATETABLE（值（Data（Skills]），ALL（Data [Skills]））
 VAR 
 Missing_Skills = COUNTROWS（不包括（Selected_Skills，Role_Skills））
 RETURN 
 Missing_Skills + 0 
  

该公式使用相同的逻辑，仅返回每个角色缺少的技能数量，而不是正确/错误状态。它将允许您显示技能列表，按缺少的技能数量与所选技能组合的顺序进行排序：

它还使您能够查看最接近满足要求的角色，即使这些角色都不完美。可能是一个理想的功能。

最后说明：在所有这些报告中，我都没有小计和总计，假设它们并不重要。如果确实需要它们，则可能需要修改公式以符合总计要求（取决于要在此处显示的内容）。

I have a table like this

Role            Skills       Resource
Data Analyst     R             A
Data Analyst     Python        A
Data Analyst     SQL           B
Business Analyst SQL           A

My Skills are on filter. I have multiple visuals on the dashboard.

And If I select SQL and Python then the results of both Data Analyst and Business Analysts are getting displayed in the visual.

But, I want it to display only the Data Analyst results because only Data Analyst has all the selected skills.

To achieve this, I think of creating a measure and putting it on visual level filter in each of the visual might help.

Update :- If I select SQL here, I get 2 distinct resources on my card visual which is relevant to resources, but If I select SQL and Python - I get 0 Resources on my card visual which is relevant to resources and 1 role count which is relevant to roles on the Role measure.

Kindly help me with creating that measure.

解决方案

Perhaps someone will suggest a more elegant way to do it; I came up with the following ideas.

Create a measure (I'll call your table "Data"):

Has All Selected Skills
=
VAR 
   Selected_Skills = ALLSELECTED ( Data[Skills] )
VAR 
   Role_Skills = CALCULATETABLE ( VALUES ( Data[Skills] ), ALL ( Data[Skills] ) )
VAR 
   Missing_Skills = COUNTROWS ( EXCEPT ( Selected_Skills, Role_Skills ) )
RETURN
   IF ( NOT ( Missing_Skills ), 1 )

If the measure is placed in a visual against Roles, it'll produce the following results:

The way this code works:

• First, we store all selected skills in a variable "Selected_Skills";
• Second, we store all skills available for a role in a variable "Role_Skills". We must use ALL(Data[Skill]) to ignore the skill slicer selections;
• Third, since both variables above are tables, we can use EXCEPT function to find how they are different. Here, we tell DAX to find what records in Selected_Skills don't exist in Role_Skills. Store the result in a variable "Missing_Skills".
• Finally, if Missing_Skills is zero, it means that the role has all selected skills, and we flag it as 1 (although you might use True/False, etc).

The problem I see with this approach is that if Skill selector has no selection (shows "all skills"), then the formula might return blank for all roles, and all your visuals will be blank. Technically, it's correct - it's essentially saying that no roles have all skills. But if that's not the behavior you want, consider a slightly modified approach:

Missing Skills Count
=
VAR 
   Selected_Skills = ALLSELECTED ( Data[Skills] )
VAR 
   Role_Skills = CALCULATETABLE ( VALUES ( Data[Skills] ), ALL ( Data[Skills] ) )
VAR 
   Missing_Skills = COUNTROWS ( EXCEPT ( Selected_Skills, Role_Skills ) )
RETURN
   Missing_Skills + 0

The formula uses the same logic, only returns the number of missing skills per role, instead of a true/false status. It will allow you to show a list of skills, sorted by the number of missing skills vs the selected skill set:

You can still use it to filter your visuals; the advantage is that it's never blank, even if all skills are selected:

It also gives you a capability to see what roles are the closest to meeting the requirement, even if none matches it perfectly; might be a desirable feature.

Final note: in all these reports, I have no subtotals and totals, assuming that they are not important. If you do need them, then the formulas might need to be modified to meet your requirements for the totals (depending on what you want to show there).

这篇关于Power BI-仅显示属于所需选择（和逻辑）的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！