Variáveis e Tabelas Virtuais em Medidas

Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

Você já ouviu falar de Variáveis e Tabelas virtuais em medidas DAX?! Sabe quando e como utilizá-las ? Conhece os principais perigos no uso de variáveis?! Bom, então continue por aqui porque você tirará todas as dúvidas sobre esse mundo e verá vários exemplos práticos de aplicação!

Antes de começar veja os lembretes:

  1. Entre os dias 1 e 5 de Fevereiro teremos uma nova Power BI Week em PT. Inscreva-se aqui: https://powerbiexperience.com/pt/pbiw6/
  2. Este artigo foi elaborado com base na live#47. As lives ocorrem toda terça-feira às 19h no meu canal do Youtube.
  3. As lives ficam disponíveis até a próxima segunda.
  4. Playlist com lives antigas: https://powerbiexperience.com/pt/playlist-lives-semanais/
  5. Download do Ebook “O Caminho para o Sucesso com Power BI”:            – https://powerbiexperience.com/arquivos/Ebook_O_Caminho_para_o_Sucesso_com_Power_BI_LeoKarpa.pdf
  6. Todas as Lives são disponibilizadas no Bônus do Power BI Experience (Curso Completo)

Agora, bora começar!

Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.

Como e por que usar variáveis em DAX?

Nosso primeiro cenário será o seuguinte: Temos uma tabela de vendas que possui essas três colunas destacadas:

Figura1: Tabela Fato de Vendas

E também temos a tabela com o custo unitário dos produtos:

Figura 2: Tabela de Custos por produto

Como você calcularia a margem bruta % de um venda com base nessas quatro colunas que te mostrei?

Esse cálculo pode ser obtido assim:

% Margem Bruta sem VAR =
DIVIDE (
    SUMX (
        Vendas,
        Vendas[QtdItens] * Vendas[ValorUnitario] – Vendas[DescontoItem]
    )
        – SUMX (
            Vendas,
            Vendas[QtdItens]
                RELATED ( Produto[CustoUnitario] )
        ),
    SUMX (
        Vendas,
        Vendas[QtdItens] * Vendas[ValorUnitario] – Vendas[DescontoItem]
    )
)

Fórmula monstra, né?! E bem chato para debugar também! Além disso, repare que há duas linhas com o termo:

SUMX(Vendas, Vendas[QtdItens] * Vendas[ValorUnitario] – Vendas

Isso significa que o Power BI vai calcular 2x essa medida e portanto irá demorar mais para processá-la.

Agora, veja que se usarmos variáveis evitaremos isso e também deixaremos nosso código mais “legível”:

% Margem Bruta com VAR =
VAR vFaturamento =
    SUMX (
        Vendas,
        Vendas[QtdItens] * Vendas[ValorUnitario] – Vendas[DescontoItem]
    )
VAR vCusto =
    SUMX (
        Vendas,
        Vendas[QtdItens]
            RELATED ( Produto[CustoUnitario] )
    )
VAR vMargem = vFaturamento – vCusto
VAR vMargemPerc =
    DIVIDE (
        vMargem,
        vFaturamento
    )
RETURN
    vMargemPerc

Comparando as duas formas de cálculo, veja que tivemos o mesmo resultado:

Figura1: Medida com e sem variável

Perceba que eu não fiz nada demais. Apenas declarei cada variável com o comando VAR e associei aos trechos das fórmulas que já tínhamos vistos na primeira equação. Para mostrar o resultado da sua variável, basta usar o comando RETURN e escolher a variável que você quer mostrar.

Podíamos ter retornado qualquer variável na última linha da fórmula para debugar cada passo da nossa medida. Isso agiliza e muito o trabalho!

Então resumindo…

Por que usar Variáveis?

Pra separar uma medida em pedaços e poder testá-la passo-a-passo (debugar mais facilmente)
Melhor performance

Como declarar e usar variáveis?

Declaramos as variáveis com a palavra-chave VAR (quantas eu quiser)
Retornamos o valor desejado após aplicar a palavra-chave RETURN

Perigos no uso de variáveis

Vamos supor que você queira calcular o percentual de faturamento por ano. Para fazer isso precisamos calcular o valor do faturamento, certo? Então teremos:

Faturamento =
SUMX (
    Vendas,
    Vendas[QtdItens] * Vendas[ValorUnitario] – Vendas[DescontoItem]
)

Colocando o resultado numa matriz, teremos:

Figura 2: Testando resultado da medida Faturamento

Agora que temos a medida Faturamento, podemos utilizá-la dentro de uma outra medida, que calculará % Fat, numa variável.

Além disso, precisamos obter o valor do Faturamento total para todas as linhas da nossa matriz – para só depois dividir o valor do faturamento pelo total.

Veja que essa é uma etapa intermediária, então vou retornar a variável vFatTotal para verificar se o valor ficou correto:

% Fat =
VAR vFaturamento = [Faturamento]
VAR vFatTotal =
    CALCULATE (
        vFaturamento,
        ALL ( Data )
    )
RETURN
    vFatTotal

Como resultado para a variável vFatTotal tivemos:

Figura 3: Resultado incorreto com variáveis

Opa, opa, opa! Algo de errado não está certo!

Não era isso que você esperava, né?! Você estava esperando que o valor de 17 mil e pouco aparecesse igual em todas as linhas da tabela, né?!

Quando você define uma variável na medida, ela fica fixa no momento que você a declarou. Isto é, se você utilizá-la dentro de um função que percorre linhas de uma tabela, por exemplo, ela não vai funcionar.

Para corrigirmos isso, você precisa substituir a variável pela medida:

% Fat =
VAR vFaturamento = [Faturamento]
VAR vFatTotal =
    CALCULATE (
        [Faturamento],
        ALL ( Data )
    )
RETURN
    vFatTotal

Agora a vFatTotal ficou:

Figura 3: Debugando variável vFatTotal

Veja que está correto o resultado, né?! Conseguimos retornar o total do faturamento em todas as linhas da tabela.

Agora que já temos o numerador e denominador, ficou fácil obter o valor % Fat, basta criarmos a terceira variável chamada vFatPer utilizando a função DIVIDE:

% Fat =
VAR vFaturamento = [Faturamento]
VAR vFatTotal =
    CALCULATE (
        [Faturamento],
        ALL ( Data )
    )
VAR vFatPerc =
    DIVIDE (
        vFaturamento,
        vFatTotal
    )
RETURN
    vFatPerc

Como resultado tivemos o seguinte:

Figura 4: Resultado final

Resumindo:

Perigos no uso de Variáveis

• Variáveis na verdade poderiam ser chamadas de Constantes.

Lembra do exemplo? Uma vez declaradas, você não consegue mudar o valor da variável.

SUPER DICA:
Não utilize variáveis no primeiro argumento da CALCULATE ou CALCULATETABLE, pois você não conseguirá mais modificar o contexto desta variável.

Desafio do Bootcamp

No último Bootcamp dos Alunos do Curso Completo, tínhamos um desafio muito semelhante a este que vamos fazer. Veja como ficou o desafio do Bootcamp:

Figura 5: TOP N Dinâmico

Aqui nós iremos fazer bem parecido.

Precisamos criar uma medida que nos retornará uma classificação de TOP N dinâmica de faturamentos por vendedor. Ao selecionar 3 no slicer, por exemplo, teremos apenas os 3 vendedores que mais venderam e uma categoria nova chamada “Outros” para alocar todos os demais vedendores que estão fora do TOP 3. Precisaremos criar uma tabela virtual para fazer isso!

Tabela Virtual

Primeiro, vamos criar um parâmetro que será onde o usuário selecionará quantos vendedores ele quer ver no rank.

Vá em Modelagem e clique no botão de Parâmetros:

Figura 6: Criando parâmetro

Perceba que precisamos inserir “1” ali em Mínimo.

Quando você criar um parâmetro, aparecerá uma tabela com um campo e uma medida. Para que o valor Máximo do parâmetro não ultrapasse a quantidade de vendedores que temos, vamos editar o campo e deixar ele assim:

TOPN =
GENERATESERIES (
    1,
    COUNTROWS ( Vendedor ),
    1
)

Massa!

Agora bora criar nossa tabela virtual para adicionar a categoria “Outros” na lista de Vendedores que temos:

VendedorOutros =
UNION (
    ALL (
        Vendedor[cdVendedor],
        Vendedor[Vendedor]
    ),
    {
         ( “9999999”“Outros” )
    }
)

Se você for em Dados, verá que a tabela virtual terá mais uma linha com “Outros”.

Agora a cereja do bolo! Note que não é num piscar de olhos que você vai entender toda a medida que colei aqui embaixo. Você precisa analisá-la com calma… Simule esse exemplo que estamos fazendo e teste! Teste cada variável que criamos! Seu aprendizado será de maior qualidade se você colocar a “Mão na massa”!

Olha como sou um cara legal: comentei toda a medida para que fique mais claro pra você qual foi nosso raciocínio durante a live #47!

Faturamento c/ Outros =
VAR vN = [TOPN Valor]
VAR vTopN =
    //Cria uma Tabela Virtual com os TOPN
    TOPN (
        vN,
        ALL ( Vendedor[Vendedor] ),
        CALCULATE (
            [Faturamento],
            ALLSELECTED ( Data )
        )
    )
VAR vFatTopN =
    //Retorna o Faturamento dos TOPN
    CALCULATE (
        [Faturamento],
        KEEPFILTERS ( vTopN ),
        TREATAS (
            //Criando um relacionamento virtual (Left Outer Join)
            VALUES ( VendedorOutros[cdVendedor] ),
            Vendas[cdVendedor]
        )
    )
VAR vVendedorContexto =
    SELECTEDVALUE ( VendedorOutros[Vendedor] )
VAR vOutros =
    EXCEPT (
        ALL ( Vendedor[Vendedor] ),
        vTopN
    ) //Retorna os Vendedores que não fazem parte do TOPN (Left Anti Join)
VAR vFatOutros =
    CALCULATE (
        [Faturamento],
        vOutros
    ) //Retorna o Faturamento dos que não fazem parte do TOPN
VAR vResultado =
    IF (
        vVendedorContexto = “Outros”,
        vFatOutros,
        vFatTopN
    )
RETURN
    IF (
        ISINSCOPE ( VendedorOutros[Vendedor] ),
        vResultado,
        [Faturamento]
    )

Agora, no visual, veja como ficou:

Figura 7: TOPN Dinâmico

Espero que tenha gostado do material!

Não é um assunto para iniciantes então se você tiver vendo DAX pela primeira vez, não se assuste! Com o tempo e muito treino os conceitos vão sendo absorvidos, beleza?! Não se esqueça que toda terça tem live gratuita no meu canal!

Se tiver sugestão para a próxima live, comenta aqui embaixo!

Abraços,
Leonardo.

Compartilhe este post:
Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest