Calculation group TMSL example

Initially I provided a TOM sample to create a calculation group, but you can also interact with the Tabular engine through TMSL using for example management studio.

So here is code snippet you can run in your management studio that points to either AS or your internal Power BI desktop file. Make sure you set the compatibility level to at least 1500 and set DiscourageImplicitMeasures to true. This you can do by scripting out the database first and making changes like this:

Now you can use a new XMLA query and create a new calculation group like this:

{
  "create": {
    "parentObject": {
      "database": "YOURDATABASENAME"
    },
    "table": {
      "name": "Time Intelligence",
      "calculationGroup": {
        "calculationItems": [
          {
            "name": "Current",
            "expression": "Selectedmeasure()",
            "ordinal": 0
          },
          {
            "name": "YTD",
            "expression": [
              "  CALCULATE (",
              "        SELECTEDMEASURE (),",
              "        DATESYTD ( 'DimDate'[DateKey] )",
              "    )"
            ],
            "ordinal": 4,
            "formatStringDefinition": {
              "expression": "\"#,0.00\""
            }
          },
          {
            "name": "YoY",
            "expression": [
              "SELECTEDMEASURE() - CALCULATE(",
              "    SELECTEDMEASURE(),",
              "    'Time Intelligence'[Time Calculation] = \"PY\"",
              ")"
            ],
            "ordinal": 2,
            "formatStringDefinition": {
              "expression": "\"#,0.00\""
            }
          },
          {
            "name": "PY",
            "expression": "CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[DateKey]))",
            "ordinal": 1,
            "formatStringDefinition": {
              "expression": "\"#,0.00\""
            }
          },
          {
            "name": "YOY%",
            "expression": [
              "DIVIDE(",
              "    CALCULATE(",
              "        SELECTEDMEASURE(),",
              "        'Time Intelligence'[Time Calculation]=\"YOY\"",
              "    ),",
              "    CALCULATE(",
              "        SELECTEDMEASURE(),",
              "        'Time Intelligence'[Time Calculation]=\"PY\"",
              "    )",
              ")"
            ],
            "ordinal": 3,
            "formatStringDefinition": {
              "expression": "\"0.00%\""
            }
          }
        ]
      },
      "columns": [
        {
          "name": "Time Calculation",
          "dataType": "string",
          "sourceColumn": "Name",
          "sortByColumn": "Ordinal",
          "summarizeBy": "none"
        },
        {
          "name": "Ordinal",
          "dataType": "int64",
          "isHidden": true,
          "sourceColumn": "Ordinal",
          "summarizeBy": "sum"
        }
      ],
      "partitions": [
        {
          "name": "Partition",
          "mode": "import",
          "source": {
            "type": "calculationGroup"
          }
        }
      ]
    }
  }
}

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.