BISM表格, 商业智能-BI, 达克斯, Power BI, Power BI桌面, 动力枢轴, 功率查询, 自助式商务智能, SQL服务器 2016

使用DAX中的表和行构造器在SSAS表格中创建自定义表

Dynamic_Measures_in_Card_Visual _-_ Power_BI_Desktop

不久前，我正在进行一个Power BI项目，客户希望在模型中直接定义一个新表。在Power BI Desktop中最简单的方法是“输入数据”，它通过键入或粘贴新内容来创建新表。前几天我在想那个项目，然后想着，嘿，当SSAS Tabular 2016中没有可用的Power Query（M）语言时，我们如何在SSAS Tabular中做同样的事情。好消息是 功率查询将在SQL Server vNext的下一版本的SSAS Tabular中可用。但是，在此之前，解决方法是将数据输入CSV文件，然后将其加载到模型中。另一种方法是使用SQL Server 2016表格模型在DAX中实现自定义表 表和行构造器。在本文中，我向您展示了一种使用DAX中的表构造函数在SSAS Tabular中创建自定义表的方法。您可以在Power BI中执行相同的原则。因此，如果您不想使用“输入数据”功能（该功能有效地使用Power Query在Power BI Desktop中创建新表），则可以使用DAX进行此操作。

要求

如果还没有SQL Server 2016，则可能是时候下载并安装它了。在本文中，我将AdventureWorksDW用作示例数据库。

情境

您参与了SSAS表格项目，客户要求使用动态卡在Power BI中提供报告，以便卡可视化视图中显示的值应根据从切片器选择的度量来动态更改。您在模型中有几种不同的度量，客户希望仅在一个Card视觉中动态显示其中的一些度量。考虑您在卡中显示以下措施：

  • 互联网销售总额
  • 2014年的互联网销售
  • 互联网销售交易总数

您必须创建一个逻辑，以便用户可以选择以上任意一种措施以在单个Card视觉中显示。

这个怎么运作

满足要求后，就可以开始在SQL Server数据工具（SSDT）中实现上述方案了。在SSAS Tabular 2016中创建计算表非常容易。我们需要做的就是创建一个包含两列的自定义表。一列存储度量的友好名称，另一列包含度量的DAX表达式。您可能已经注意到，我在谈论在DAX中创建自定义表格并将其填充值。继续阅读以了解操作方法。我们要做的是使用DAX中的表构造函数创建一个计算表。 表和行构造器 在SSAS表格中的DAX的早期版本中不可用。它们非常类似于 清单 或清单 元组 就像我们在MDX中拥有的一样。

稍后，当我们在SSDT中创建示例模型时，我将对此进行解释。

在SSDT中创建新的SSAS表格项目

正如我之前所说的，需要SSAS Tabular 2016来实现以下方法。原因是我们必须在模型中创建一个计算表，并且该计算表在SSAS Tabular的早期版本中不可用。

  • 打开SSDT
  • 创建一个新的SSAS表格项目

  • 单击“集成工作区”，然后单击确定

  • 单击“模型”菜单，然后单击“从数据源导入”
  • 输入SQL Server名称，然后选择AdventureWorksDW，然后单击“下一步”。

  • 输入“模拟信息”，然后单击“下一步”。

  • 保留默认值，然后单击下一步
  • 选择“ FactInternetSales”和“ DimDate”，然后单击“完成”。

到目前为止，我们在SSDT 2015中创建了一个SSAS Tabular项目，并将FactInternetSales加载到该模型中。

创建所需的措施
在这种情况下，我们需要创建3个新的度量，如下所示：

互联网销售总额 := SUM ( 'FactInternetSales'[SalesAmount] )
Internet Sales In 2014 := CALCULATE(SUM( 'FactInternetSales'[SalesAmount] ), 'DimDate'[CalendarYear] = 2014)
互联网销售交易总数 := COUNTROWS ( 'FactInternetSales' )

使用表和行构造器DAX创建自定义表

在SSDT中创建计算表相对容易。我们只需要单击相应的按钮并使用表和行构造器创建一个新表。让我解释 表 and 行 建设者 一点点。

达克斯中的表构造器

表构造函数是DAX中的一项新功能，非常酷。要构建表，我们需要将值放在大括号中。例如，要创建一个表“ 蓝色”，“ 橙子”，“ 红”，我们只需要编写以下内容：

{“Blue”, “Orange”, “Red”}

要查看它如何在DAX中工作：

  • 在SSDT中，右键单击“解决方案资源管理器”中的模型，然后单击“属性”

  • 从SSDT复制工作区服务器

  • 打开SQL Server管理Studio2016（SSMS）
  • 粘贴您从SSDT复制的工作区服务器地址，以连接到SSAS Tabular的本地实例

  • 打开一个新的MDX查询并运行以下DAX查询
EVALUATE
    {"RED", "BLUE","ORANGE"}

是的，您在MDX查询编辑器中运行了DAX查询，因为SSMS中当前没有DAX查询编辑器。

结果显示了蓝色，橙色和红色的表格。

达克斯中的行构造器

要构造一行，我们可以将值放在括号中，然后将整个值封装在大括号中，例如{（“ 蓝色”，Orange”，“ 红”）}。在SSMS中运行以下DAX查询以查看结果：

EVALUATE
    {("蓝色", "橙子", "红")}

在DAX中组合表和行构造器

既然您已经知道如何构造表格或行，就可以轻松地将两者结合在一起。在SSMS中运行以下DAX查询。

EVALUATE
    {("Banana", "Yellow"), ("Cucumber", "Green")}

如您所见，有自动添加的列名。但是，它们并不是真正有用的。因此，让我们在列中添加一些有意义的名称。我们可以轻松地在DAX中使用SELECTCOLUMNS（）函数来实现此目的。运行以下DAX查询并查看结果：

EVALUATE
    SELECTCOLUMNS(
       {("Banana", "Yellow"), ("Cucumber", "Green")}
       , "Fruit Name", [Value1]
       , "Colour", [Value2]
       )

辉煌。

现在，使用上述技术来实现我们的方案。我们需要做的是创建一个包含度量名称和实际度量本身的自定义表。在SSMS中复制，粘贴并运行以下DAX查询：

有趣吗？上面的查询所做的是，它运行了我们前面定义的显式度量，并将结果放入表中。

下一步是使用SELECTCOLUMNS（）重命名列。下面的查询完成了这项工作：

让我们回到SSDT中的解决方案，并使用上述技术创建一个计算表。

在SSAS表格中创建计算表

  • 切换回SSDT中的SSAS表格解决方案，然后单击 创建一个计算表

  • 复制并粘贴以下DAX表达式
=SELECTCOLUMNS(
             {("互联网销售总额", [Total Internet Sales])
             , ("Internet Sales In 2014", [Internet Sales In 2014])
             , ("互联网销售交易总数", [互联网销售交易总数])}
             , "Measure Name", [Value1]
             , "Measure Results", [Value2]
             )

  • 您可以通过双击表选项卡来重命名计算表

到目前为止，我们已经成功创建了一个包含度量名称及其值的计算表。

记得： 我们应该以可视化方式在单个Card视觉中显示动态度量。表示层可以是Power BI，Excel等。

为了能够实现动态显示度量的目标，我们需要创建一个新度量，该度量基本上显示最终用户所选度量的值。

  • 创建一个新度量并复制/粘贴以下DAX表达式：
动态测度:= CALCULATE(VALUES('Dynamic Card'[Measure Results])
            , filter(ALLSELECTED('Dynamic Card'[Measure Name])
                , 'Dynamic Card'[Measure Name]=[Measure Name]))

创建新度量后，您会立即收到以下错误消息：

“ MdxScript（Model）（1，59）计算中的错误‘动态卡的“动态度量”：提供了多个值的表，其中预期有一个值。”

我们收到上述错误消息的原因是我们将多个值传递给度量，而不是在过滤器部件中传递单个值。我们可以在过滤器中放置一个恒定值，但这不是我们想要的。查看DAX表达式，您会很快发现我们已经使用ALLSELECTED（）根据最终用户在报告中选择的内容来过滤值。因此，我们稍后需要在报告上放置一个切片器。因此，不必担心错误消息。但是，请记住，用户应仅从切片器中选择一个值，因此我们需要将切片器设置为单选。

让我们在Excel中浏览我们的SSAS表格模型。

在Excel中浏览SSAS表格

您可以轻松地在Excel中浏览模型以测试我们正在寻找的功能。

  • 单击SSDT上的Excel按钮以打开Excel并浏览模型

注意： 您需要在计算机上安装Microsoft 电子表格才能在Excel中浏览模型。

  • 在“在Excel中分析”窗口上单击“确定”。
  • 点击“动态度量”
  • 您会立即收到一条错误消息

  • 右键单击字段列表中的“度量名称”，然后单击“添加为切片器”

  • 现在在切片器中选择一个项目

这就是您得到的……成功！

从Power BI连接到SSAS表格

  • 打开Power BI桌面
  • 从SQL Server Analysis Services获取数据
  • 将SSDT中的工作区服务器用作服务器名称（您已在本文前面学习了如何做。简而言之，您可以在模型属性中找到它）
  • 实时连接到模型
  • 在报表上放置汽车视觉效果，然后勾选“动态措施”
  • 您会收到一条错误消息，您知道
  • 在报告中添加切片器，然后选择“度量名称”
  • 从切片机中选择一个项目

格式化动态测度

到目前为止，我们已经实现了目标，但是，正如您所看到的，结果并没有那么有用。当您从切片器中选择其他项目时，“名片”视觉中显示的值本身并不能提供足够的信息。因此，让我们为“动态度量”添加一些格式。为此，我们需要修改用于构造计算表的DAX表达式。

  • 切换回SSDT
  • 单击“动态卡”选项卡，然后如下修改DAX表达式
=  SELECTCOLUMNS(             {("互联网销售总额", FORMAT([Total Internet Sales], "Currency"))           , ("Internet Sales In 2014", FORMAT([Internet Sales In 2014], "Currency"))              , ("互联网销售交易总数", FORMAT([互联网销售交易总数] ,"0,0"))}            , "Measure Name", [Value1]              , "Measure Results", [Value2]           )

  • 切换回Power BI Desktop并刷新数据

  • 看起来不错，但是，如果您像我一样使用OCD，则最好使用以下DAX表达式，该表达式会向度量添加一些文本以使其易于说明

  • 在Power BI Desktop中再次刷新数据，这就是您得到的
  • 组“Word wrap” to “On” for Card visual

工作完成了！

这是我在2016年的最后一篇文章，所以祝大家圣诞节快乐。

明年再见！

 

由...出版 索海尔·巴赫希（Soheil Bakhshi）

2 thoughts on “使用DAX中的表和行构造器在SSAS表格中创建自定义表”

  1. 嗨，您好。一世’感兴趣地阅读了您的帖子…但是这种方法有问题—这些措施根本不是动态的。也就是说，当您在其中一列中创建具有其值的表时，这些值将设置为石头。无论您的条件如何’d例如，希望在Power BI画布上进行分割，不会对图形产生丝毫影响（而且应该如此）。这可能不是你’d like to see. You’d您想采取的措施恰恰相反，为此，您不能使用上面的技术。但是，有一些方法可以实现完全动态的措施，从而可以对其他选择做出优雅的反应。但是，这种技术不能使用计算表，因为这些表是一劳永逸的—模型加载时。

    回复

    1. 嗨，达雷克，
      欢迎使用BIInsight.com，并感谢您的反馈。
      请注意，在这种方法中，我们正在创建一个计算表，这意味着仅当您处理引用表时该表的数据才会更新。在描述的场景中，我们将在“ FactInternetSales”的顶部创建一个计算表。因此，仅当您处理引用表（在我们的情况下为“ FactInternetSales”）时，才会更改计算表的数据。
      当然，这只是许多其他方法中的一种，您可以实现类似或更复杂的方案。
      干杯。 

      回复

有什么想法吗？在这里与我们分享：

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.