这里的关键是为切片器提供不同的不相关表。
让我们调用你的原始表Jobs
.
创建两个新表:
Role = DISTINCT(Jobs[Role])
Skills = DISTINCT(Jobs[Skills])
现在我们有了这些表,我们可以用它们创建切片器并将所选值读入我们的度量中。
% Skill Match =
VAR SelectedRole = SELECTEDVALUE ( Role[Role] )
VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )
VAR CurrentSkills = DISTINCT ( Jobs[Skills] )
RETURN
DIVIDE (
COUNTROWS ( INTERSECT ( RelatedSkills, CurrentSkills ) ),
COUNTROWS ( CurrentSkills )
)
这将读取您在第一个变量中选择的角色。当我们提高技能时,我们也会在另一个切片器中读取:
% Skill Match Upskilled =
VAR SelectedRole = SELECTEDVALUE ( Role[Role] )
VAR SelectedSkills = VALUES ( Skills[Skills] )
VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )
VAR CurrentSkills = DISTINCT ( Jobs[Skills] )
VAR Upskilled = DISTINCT ( UNION ( RelatedSkills, SelectedSkills ) )
RETURN
DIVIDE (
COUNTROWS ( INTERSECT ( Upskilled, CurrentSkills ) ),
COUNTROWS ( CurrentSkills )
)
未使用的技能测量非常相似。
Unused Skills =
VAR SelectedRole = SELECTEDVALUE ( Role[Role] )
VAR SelectedSkills = VALUES ( Skills[Skills] )
VAR RelatedSkills = CALCULATETABLE ( DISTINCT ( Jobs[Skills] ), Jobs[Role] = SelectedRole )
VAR CurrentSkills = DISTINCT ( Jobs[Skills] )
VAR Upskilled = DISTINCT ( UNION ( RelatedSkills, SelectedSkills ) )
RETURN
CONCATENATEX ( EXCEPT ( Upskilled, CurrentSkills ), Jobs[Skills], ", " )
结果应该是这样的:
您可以添加一些逻辑来隐藏您在矩阵视觉中选择的角色,但我在这里让事情变得更简单。