Grupos de cálculo

Aplica-se a: SQL Server 2019 e posterior Analysis Services Azure Analysis Services Power bi Premium

Os grupos de cálculo podem reduzir significativamente o número de medidas redundantes agrupando expressões de medida comuns como itens de cálculo. Os grupos de cálculo têm suporte em modelos de tabela no nível de compatibilidade1500 e superior.

Este artigo descreve:

  • Benefícios
  • Como funcionam os grupos de cálculo
  • Cadeias de caracteres de formato dinâmico
  • Precedência
  • Recursão lateral
  • Ordenando
  • Como criar
  • Limitações

Benefícios

Os grupos de cálculo abordam um problema em modelos complexos em que pode haver uma proliferação de medidas redundantes usando os mesmos cálculos – mais comuns com cálculos de inteligência de tempo. Por exemplo, um analista de vendas deseja exibir os totais de vendas e os pedidos por mês-até-data (MTD), trimestre acumulado (QTD.), ano até a data, pedidos desde o ano anterior (PY) e assim por diante. O modelador de dados precisa criar medidas separadas para cada cálculo, o que pode levar a dezenas de medidas. Para o usuário, isso pode significar ter que classificar apenas quantas medidas e aplicá-las individualmente ao relatório.

Vamos primeiro dar uma olhada em como os grupos de cálculo aparecem para os usuários em uma ferramenta de relatório como Power BI. Em seguida, vamos dar uma olhada no que compõe um grupo de cálculo e como eles são criados em um modelo.

Grupos de cálculos são mostrados em clientes de relatório como uma tabela com uma única coluna. A coluna não é como uma coluna ou dimensão típica, mas representa um ou mais cálculos reutilizáveis ou itens de cálculo que podem ser aplicados a qualquer medida já adicionada ao filtro de valores para uma visualização.

Na animação a seguir, um usuário está analisando dados de vendas por anos 2012 e 2013. Antes de aplicar um grupo de cálculo, as vendas da medida de base comum calculam uma soma do total de vendas para cada mês. Em seguida, o usuário deseja aplicar cálculos de inteligência de tempo para obter os totais de vendas do mês até a data, trimestre até a data, desde o início do ano e assim por diante. Sem grupos de cálculo, o usuário teria que selecionar medidas de inteligência de tempo individuais.

Com um grupo de cálculo, neste exemplo denominado inteligência de tempo, quando o usuário arrasta o item de cálculo de tempo para a área de filtro de colunas , cada item de cálculo aparece como uma coluna separada. Os valores para cada linha são calculados a partir da medida base, vendas.

Grupo de cálculo sendo aplicado no Power BI

Grupos de cálculo funcionam com medidas Dax explícitas . Neste exemplo, Sales é uma medida explícita já criada no modelo. Os grupos de cálculo não funcionam com medidas DAX implícitas. Por exemplo, em Power BI medidas implícitas são criadas quando um usuário arrasta colunas para visuais para exibir valores agregados, sem criar uma medida explícita. Neste momento, Power BI gera DAX para medidas implícitas escritas como cálculos DAX embutidos, o que significa que as medidas implícitas não funcionam com grupos de cálculo. Uma nova propriedade de modelo visível no modelo de objeto de tabela (TOM) foi introduzida, DiscourageImplicitMeasures. No momento, para criar grupos de cálculo, essa propriedade deve ser definida como true. Quando definido como true, Power BI Desktop no modo Live Connect desabilita a criação de medidas implícitas.

Os grupos de cálculo também dão suporte a consultas MDX (multidimensional data Expressions). Isso significa que os usuários do Microsoft Excel, que consultam modelos de dados tabulares usando MDX, podem aproveitar ao máximo os grupos de cálculo em gráficos e tabelas dinâmicas de planilha.

Como eles funcionam

Agora que você viu como os grupos de cálculo beneficiam os usuários, vamos dar uma olhada em como o exemplo de grupo de cálculo de inteligência de tempo mostrado é criado.

Antes de entrarmos nos detalhes, vamos introduzir algumas novas funções DAX especificamente para grupos de cálculo:

SELECTEDMEASURE – usado por expressões para itens de cálculo para referenciar a medida que está atualmente no contexto. Neste exemplo, a medida Sales.

SELECTEDMEASURENAME – usado por expressões para itens de cálculo para determinar a medida que está no contexto por nome.

ISSELECTEDMEASURE – usado por expressões para itens de cálculo para determinar a medida que está no contexto é especificada em uma lista de medidas.

SELECTEDMEASUREFORMATSTRING – usado por expressões para itens de cálculo para recuperar a cadeia de caracteres de formato da medida que está no contexto.

Exemplo de inteligência de tempo

Nome da tabela- inteligência de tempo
Nome da coluna- cálculo de tempo
Precedência- 20

Itens de cálculo de inteligência de tempo

Atualizados

SELECTEDMEASURE()

MTD

CALCULATE(SELECTEDMEASURE(), DATESMTD(DimDate[Date]))

QTD

CALCULATE(SELECTEDMEASURE(), DATESQTD(DimDate[Date]))

YTD

CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))

PY

CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR(DimDate[Date]))

PY MTD

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "MTD"
)

QTD. DO PY

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "QTD"
)

AJ POR ANO

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "YTD"
)

YOY

SELECTEDMEASURE() -
CALCULATE(
    SELECTEDMEASURE(),
    'Time Intelligence'[Time Calculation] = "PY"
)

YOY

DIVIDE(
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="YOY"
    ),
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="PY"
    )
)

Para testar esse grupo de cálculo, execute uma consulta DAX no SSMS ou o Dax Studiode código aberto. Observação: YOY e YOY% são omitidos deste exemplo de consulta.

Consulta de inteligência de tempo

EVALUATE
CALCULATETABLE (
    SUMMARIZECOLUMNS (
        DimDate[CalendarYear],
        DimDate[EnglishMonthName],
        "Current", CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "Current" ),
        "QTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "QTD" ),
        "YTD",     CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "YTD" ),
        "PY",      CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" ),
        "PY QTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY QTD" ),
        "PY YTD",  CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY YTD" )
    ),
    DimDate[CalendarYear] IN { 2012, 2013 }
)

Retorno de consulta de inteligência de tempo

A tabela de retorno mostra os cálculos para cada item de cálculo aplicado. Por exemplo, consulte QTD para março de 2012 é a soma de Janeiro, fevereiro e 2012 de março.

Retorno de consulta de inteligência de tempo

Cadeias de caracteres de formato dinâmico

Cadeias de formato dinâmico com grupos de cálculo permitem a aplicação condicional de cadeias de caracteres de formato a medidas sem forçá-las a retornar cadeias de caracteres

Os modelos de tabela dão suporte à formatação dinâmica de medidas usando a função de formato Dax. No entanto, a função FORMAT tem a desvantagem de retornar uma cadeia de caracteres, forçando medidas que, de outra forma, seriam numéricas também para serem retornadas como uma cadeia de caracteres. Isso pode ter algumas limitações, como não trabalhar com a maioria dos Power BI visuais, dependendo dos valores numéricos, como gráficos.

Cadeias de caracteres de formato dinâmico para inteligência de tempo

Se observarmos o exemplo de inteligência de tempo mostrado acima, todos os itens de cálculo, exceto yoy% , devem usar o formato da medida atual no contexto. Por exemplo, calculado acumulado na medida de base de vendas deve ser moeda. Se esse fosse um grupo de cálculo para algo como uma medida base de pedidos, o formato seria numérico. Yoy%, no entanto, deve ser uma porcentagem, independentemente do formato da medida base.

Para yoy%, podemos substituir a cadeia de caracteres de formato definindo a propriedade Format String Expression como 0,00%;-0,00%; 0,00%. Para saber mais sobre propriedades de expressão de cadeia de caracteres de formato, consulte Propriedades da célula MDX – conteúdo da cadeia de caracteres de formato.

Neste visual de matriz no Power BI, você vê Sales Current/yoy e Orders Current/yoy retêm suas respectivas cadeias de caracteres de formato de medida base. Sales yoy% e Orders yoy%, no entanto, substitui a cadeia de caracteres de formato para usar o formato de porcentagem .

Inteligência de tempo no Visual de matriz

Cadeias de caracteres de formato dinâmico para conversão de moeda

Cadeias de caracteres de formato dinâmico fornecem uma conversão de moeda fácil. Considere o modelo de dados do Adventure Works a seguir. Ele é modelado para conversão de moeda de um para muitos , conforme definido por tipos de conversão.

Taxa de moeda no modelo de tabela

Uma coluna FormatString é adicionada à tabela DimCurrency e preenchida com cadeias de caracteres de formato para as respectivas moedas.

Coluna de cadeia de caracteres de formato

Neste exemplo, o seguinte grupo de cálculo é definido como:

Exemplo de conversão de moeda

Nome da tabela- conversão de moeda
Nome da coluna – cálculo de conversão
Precedência- 5

Itens de cálculo para conversão de moeda

Sem conversão

SELECTEDMEASURE()

Moeda convertida

IF(
    //Check one currency in context & not US Dollar, which is the pivot currency:
    SELECTEDVALUE( DimCurrency[CurrencyName], "US Dollar" ) = "US Dollar",
    SELECTEDMEASURE(),
    SUMX(
        VALUES(DimDate[Date]),
        CALCULATE( DIVIDE( SELECTEDMEASURE(), MAX(FactCurrencyRate[EndOfDayRate]) ) )
    )
)

Expressão de cadeia de caracteres de formato

SELECTEDVALUE(
    DimCurrency[FormatString],
    SELECTEDMEASUREFORMATSTRING()
)

A expressão de cadeia de caracteres de formato deve retornar uma cadeia de caracteres escalar. Ele usa a nova função SELECTEDMEASUREFORMATSTRING para reverter para a cadeia de caracteres de formato de medida base se houver várias moedas no contexto de filtro.

A animação a seguir mostra a conversão de moeda de formato dinâmico da medida vendas em um relatório.

Cadeia de caracteres de formato dinâmico de conversão de moeda aplicada

Precedência

Precedência é uma propriedade definida para um grupo de cálculo. Especifica a ordem de avaliação quando há mais de um grupo de cálculo. Um número mais alto indica maior precedência, o que significa que ele será avaliado antes dos grupos de cálculo com precedência mais baixa.

Para este exemplo, usaremos o mesmo modelo que o exemplo de inteligência de tempo acima, mas também adicionamos um grupo de cálculo de médias . O grupo de cálculo de médias contém cálculos médios que são independentes da inteligência de tempo tradicional, pois eles não alteram o contexto de filtro de data. eles simplesmente aplicam cálculos médios dentro dele.

Neste exemplo, um cálculo de média diária é definido. Cálculos como a média de cilindros de óleo por dia são comuns em aplicativos de petróleo e gás. Outros exemplos de negócios comuns incluem média de vendas da loja no varejo.

Embora esses cálculos sejam calculados independentemente dos cálculos de inteligência de tempo, pode haver um requisito para combiná-los. Por exemplo, um usuário pode querer ver cilindros de óleo por dia até exibir a taxa diária de óleo desde o início do ano até a data atual. Nesse cenário, a precedência deve ser definida para itens de cálculo.

Exemplo de médias

O nome da tabela é a média.
O nome da coluna é cálculo médio.
A precedência é 10.

Itens de cálculo para médias

Sem média

SELECTEDMEASURE()

Média diária

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

Aqui está um exemplo de uma consulta DAX e a tabela de retorno:

Consulta de médias

EVALUATE
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
        DimDate[CalendarYear],
        DimDate[EnglishMonthName],
        "Sales", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "Current",
            'Averages'[Average Calculation] = "No Average"
        ),
        "YTD", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "YTD",
            'Averages'[Average Calculation] = "No Average"
        ),
        "Daily Average", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "Current",
            'Averages'[Average Calculation] = "Daily Average"
        ),
        "YTD Daily Average", CALCULATE (
            [Sales],
            'Time Intelligence'[Time Calculation] = "YTD",
            'Averages'[Average Calculation] = "Daily Average"
        )
    ),
    DimDate[CalendarYear] = 2012
)

Retorno de consulta de médias

Retorno de consulta de médias

A tabela a seguir mostra como os valores de março de 2012 são calculados.

Nome da coluna Cálculo
YTD Soma de vendas para Jan, Fev, mar 2012
= 495.364 + 506.994 + 373.483
Média diária Vendas para mar 2012 divididas por n º de dias em março
= 373.483/31
Média diária acumulada no ano No ano para mar 2012 dividido por n º de dias em Jan, fev e mar
= 1.375.841/(31 + 29 + 31)

Aqui está a definição do item de cálculo do ano, aplicada com a precedência de 20.

CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date]))

Aqui está a média diária, aplicada com uma precedência de 10.

DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate))

Como a precedência do grupo de cálculo de inteligência de tempo é maior do que a do grupo de cálculo de médias, ela é aplicada o mais amplamente possível. O cálculo da média diária no ano se aplica ao ano até o numerador e o denominador (contagem de dias) do cálculo da média diária.

Isso é equivalente à seguinte expressão:

CALCULATE(DIVIDE(SELECTEDMEASURE(), COUNTROWS(DimDate)), DATESYTD(DimDate[Date]))

Não é esta expressão:

DIVIDE(CALCULATE(SELECTEDMEASURE(), DATESYTD(DimDate[Date])), COUNTROWS(DimDate)))

Recursão lateral

No exemplo de inteligência de tempo acima, alguns dos itens de cálculo referem-se a outros no mesmo grupo de cálculo. Isso é chamado de recursão lateral. Por exemplo, yoy% faz referência a yoy e py.

DIVIDE(
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="YOY"
    ),
    CALCULATE(
        SELECTEDMEASURE(),
        'Time Intelligence'[Time Calculation]="PY"
    )
)

Nesse caso, ambas as expressões são avaliadas separadamente porque estão usando diferentes instruções Calculate. Não há suporte para outros tipos de recursão.

Item de cálculo único no contexto de filtro

No nosso exemplo de inteligência de tempo, o item de cálculo de py no ano tem uma única expressão Calculate:

CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR(DimDate[Date]),
    'Time Intelligence'[Time Calculation] = "YTD"
)

O argumento do ano para a função CALCULATE () substitui o contexto do filtro para reutilizar a lógica já definida no item de cálculo do ano. Não é possível aplicar PY e no ano em uma única avaliação. Os grupos de cálculo são aplicados somente se um único item de cálculo do grupo de cálculo estiver no contexto de filtro.

Ordenando

Por padrão, quando uma coluna de um grupo de cálculo é colocada em um relatório, os itens de cálculo são ordenados alfabeticamente por nome no relatório. A ordem na qual os itens de cálculo aparecem em um relatório pode ser alterada especificando a propriedade ordinal. A especificação da ordem dos itens de cálculo com a propriedade ordinal não altera a precedência, a ordem na qual os itens de cálculo são avaliados. Ele também não altera a ordem na qual os itens de cálculo aparecem no Gerenciador de modelos de tabela.

Para especificar a propriedade ordinal para itens de cálculo, você deve adicionar uma segunda coluna ao grupo de cálculo. Ao contrário da coluna padrão em que o tipo de dados é texto, uma segunda coluna usada para ordenar itens de cálculo tem um tipo de dados de número inteiro. A única finalidade dessa coluna é especificar a ordem numérica na qual os itens de cálculo no grupo de cálculo aparecem. Como essa coluna não fornece nenhum valor em um relatório, é melhor definir a propriedade Hidden como true.

Coluna para ordenação

Depois que uma segunda coluna é adicionada ao grupo de cálculo, você pode especificar o valor da propriedade ordinal para os itens de cálculo que deseja solicitar.

Propriedade ordinal

Para saber mais, consulte para ordenar itens de cálculo.

Criar um grupo de cálculo

Os grupos de cálculo têm suporte no Visual Studio com Analysis Services projetos do VSIX Update 2.9.2 e posterior. Os grupos de cálculo também podem ser criados usando TMSL (linguagem de script de modelo de tabela) ou o Editor de tabelade código aberto.

Para criar um grupo de cálculo usando o Visual Studio

  1. No Gerenciador de modelos de tabela, clique com o botão direito do mouse em grupos de cálculo e clique em novo grupo de cálculo. Por padrão, um novo grupo de cálculo terá uma única coluna e um único item de cálculo.

  2. Use Propriedades para alterar o nome e insira uma descrição para o grupo de cálculo, a coluna e o item de cálculo padrão.

  3. Para inserir uma expressão de fórmula DAX para o item de cálculo padrão, clique com o botão direito do mouse e clique em Editar fórmula para abrir o editor Dax. Insira uma expressão válida.

  4. Para adicionar itens de cálculo adicionais, clique com o botão direito do mouse em itens de cálculo e clique em novo item de cálculo.

Para ordenar itens de cálculo

  1. No Gerenciador de modelos de tabela, clique com o botão direito do mouse em um grupo de cálculo e clique em adicionar coluna.

  2. Nomeie o ordinal da coluna (ou algo semelhante), insira uma descrição e, em seguida, defina a propriedade Hidden como true.

  3. Para cada item de cálculo que você deseja ordenar, defina a propriedade ordinal como um número positivo. Cada número é sequencial, por exemplo, um item de cálculo com uma propriedade ordinal 1 aparecerá primeiro, uma propriedade de 2 será exibida segundo, e assim por diante. Os itens de cálculo com o padrão-1 não são incluídos na ordenação, mas aparecerão antes dos itens ordenados em um relatório.

Limitações

Não há suporte para a OLS ( segurança em nível de objeto ) definida em tabelas de grupo de cálculo. No entanto, OLS pode ser definido em outras tabelas no mesmo modelo. Se um item de cálculo se referir a um objeto OLS protegido, um erro genérico será retornado.

Não há suporte para a RLS ( segurança em nível de linha ). Defina a RLS em tabelas no mesmo modelo, mas não nos próprios grupos de cálculo (direta ou indiretamente).

Não há suporte para expressões de linhas de detalhes com grupos de cálculo.

Não há suporte para visuais de narração inteligente em Power bi com grupos de cálculo.

Não há suporte para agregações de coluna implícita em Power BI para modelos com grupos de cálculo. Atualmente, se a propriedade DiscourageImplicitMeasures for definida como false (padrão), as opções de agregação serão exibidas, no entanto, elas não poderão ser aplicadas. Se DiscourageImplicitMeasures for definido como true, as opções de agregação não serão exibidas.

Consulte também

DAX em modelos de tabela
Referência DAX