Lookupvalue com vários valores no DAX

Lookupvalue com vários valores no DAX

Existem vários cenários no DAX em que você precisa de um valor de uma tabela de “pesquisa” que não esteja conectada por meio de um relacionamento (o que permitiria o uso da função RELATED ). Por exemplo, considere as duas tabelas a seguir.

A tabela Sales contém várias transações:

Cliente	produtos	Encontro: Data	Quantidade
Marco	Mouse	20/01/2022	2
Marco	Tábua	16/02/2022	1
Alberto	Mouse	30/01/2022	1
Alberto	Tábua	30/01/2022	1
Alberto	Assistir	23/02/2022	1

A tabela Promo contém várias promoções, com uma chave primária que corresponde a Mês e Produto.

Mês	produtos	Campanha	meios de comunicação
1	Mouse	Pacote	Rádio
1	Tábua	Pacote	Bandeira
1	Assistir	Dois por um	Boletim de Notícias
2	Mouse	Oferta	Revista
2	Assistir	Oferta	Boletim de Notícias

Se você criar as colunas Campanha e Mídia para cada transação de Vendas em uma expressão de tabela no DAX, poderá usar a abordagem a seguir, que corresponde ao que você escreveria em duas colunas calculadas na tabela Vendas.

EVALUATE
ADDCOLUMNS (
    Sales,
    "Campaign",
    VAR MonthNumber =
        MONTH ( Sales[Date] )
    RETURN
        LOOKUPVALUE (
            Promo[Campaign],
            Promo[Product], Sales[Product],
            Promo[Month], MonthNumber
        ),
    "Media",
    VAR MonthNumber =
        MONTH ( Sales[Date] )
    RETURN
        LOOKUPVALUE (
            Promo[Media],
            Promo[Product], Sales[Product],
            Promo[Month], MonthNumber
        )
)

A função LOOKUPVALUE recupera os dois valores, Campanha e Mídia. Como você pode ver, há uma grande quantidade de código duplicado para as duas colunas. Também do ponto de vista do desempenho, o mecanismo cria duas subconsultas diferentes e independentes para recuperar os valores das duas colunas. A situação piora se você precisar de mais colunas.

Usando GENERATE e ROW

Você pode salvar alguma linha de código e melhorar o desempenho usando uma abordagem baseada nas funções GENERATE e ROW .

EVALUATE
GENERATE (
    Sales,
    CALCULATETABLE (
        ROW (
            "Campaign", VALUES ( Promo[Campaign] ),
            "Media", VALUES ( Promo[Media] )
        ),
        TREATAS ( ROW ( "Month", MONTH ( Sales[Date] ) ), Promo[Month] ),
        TREATAS ( ROW ( "Product", Sales[Product] ), Promo[Product] )
    )

A TREATAS transfere o contexto do filtro da linha atual de Vendas para a tabela Promo. A presença de VALUES na função ROW garante que, no caso de vários resultados, a consulta falhe, assim como LOOKUPVALUE (você não deseja fornecer resultados errados se houver dados incorretos).
Ao usar ROW , garantimos que sempre haverá uma linha, mesmo quando não houver linhas correspondentes na tabela Promo. Isso é importante porque queremos exibir um valor em branco para Campanha e Mídia caso não haja linhas encontradas em Promo para uma determinada transação em Vendas. Usando GENERATE , uma linha em Sales seria removida do resultado caso o segundo argumento de GENERATE não retornaria nenhuma linha. Lembre-se disso olhando para o exemplo a seguir.

Usando GENERATEALL e SELECTCOLUMNS

Se você pode confiar em seus dados e sabe que para uma determinada combinação de mês e produto não pode haver mais de uma linha em Promo, você pode usar essa outra sintaxe, que também é mais rápida:

EVALUATE
GENERATE (
    Sales,
    CALCULATETABLE (
        ROW (
            "Campaign", VALUES ( Promo[Campaign] ),
            "Media", VALUES ( Promo[Media] )
        ),
        TREATAS ( ROW ( "Month", MONTH ( Sales[Date] ) ), Promo[Month] ),
        TREATAS ( ROW ( "Product", Sales[Product] ), Promo[Product] )
    )
)

Nesse caso, todas as linhas correspondentes na tabela Promo são retornadas e SELECTCOLUMNS retorna apenas as colunas Campanha e Mídia desejadas, ocultando as colunas Mês e Produto que seriam apenas redundantes. Você pode usar essa abordagem como uma maneira de unir duas tabelas usando várias colunas. No entanto, ao usar GENERATE você não vê no resultado as linhas em Sales que não possuem linhas correspondentes em Promo. Na verdade, o resultado anterior tem apenas quatro linhas em vez de cinco.

O próximo exemplo usa GENERATEALL em vez de GENERATE , portanto, o resultado conterá todas as linhas de Sales, mesmo quando não houver linhas correspondentes em Promo. A função GENERATEALL não era necessária nos exemplos anteriores, porque a função ROW sempre retorna uma única linha.

EVALUATE
GENERATEALL (
    Sales,
    CALCULATETABLE (
        SELECTCOLUMNS (
            Promo,
            "Campaign", Promo[Campaign],
            "Media", Promo[Media]
        ),
        TREATAS ( ROW ( "Month", MONTH ( Sales[Date] ) ), Promo[Month] ),
        TREATAS ( ROW ( "Product", Sales[Product] ), Promo[Product] )
    )
)

Por fim, se você tiver uma versão do DAX que não suporte TREATAS , poderá usar INTERSECT (mas TREATAS é a melhor prática, também do ponto de vista do desempenho).

EVALUATE
GENERATEALL (
    Sales,
    CALCULATETABLE (
        SELECTCOLUMNS (
            Promo,
            "Campaign", Promo[Campaign],
            "Media", Promo[Media]
        ),
        INTERSECT ( ALL ( Promo[Month] ), ROW ( "Month", MONTH ( Sales[Date] ) ) ),
        INTERSECT ( ALL ( Promo[Product] ), ROW ( "Product", Sales[Product] ) )
    )
)

Usando NATURALLEFTJOIN

A junção entre duas tabelas também pode ser obtida usando as duas funções DAX NATURALINNERJOIN e NATURALLEFTJOIN. No entanto, essas funções exigem unir colunas com o mesmo nome, tipo e linhagem. Este último requisito não permite o uso de colunas nativas do modelo, portanto, é necessário remover a linhagem de dados das colunas envolvidas na junção, por exemplo, usando uma expressão em SELECTCOLUMNS . No exemplo a seguir, as colunas Mês e Produto usadas para unir as duas tabelas não possuem a mesma linhagem das colunas nativas correspondentes.

EVALUATE
NATURALLEFTOUTERJOIN (
    SELECTCOLUMNS (
        Sales,
        "Customer", Sales[Customer],
        "Date", Sales[Date],
        "Product", "" & Sales[Product],
        "Month", MONTH ( Sales[Date] )
    ),
    SELECTCOLUMNS (
        Promo,
        "Product", Promo[Product] & "",
        "Month", 0 + Promo[Month],
        "Campaign", Promo[Campaign],
        "Media", Promo[Media]
    )
)

Por esse motivo, NATURALINNERJOIN e NATURALLEFTJOIN são mais úteis quando você cria tabelas como resultado de outras expressões de tabela que não retornam colunas nativas.

Conclusão

No DAX, você não tem um operador de junção real entre duas tabelas, o que seria útil para recuperar dados de várias colunas de uma tabela de pesquisa. As funções NATURALINNERJOIN e NATURALLEFTJOIN não são a melhor escolha para unir duas tabelas físicas. A função LOOKUPVALUE é uma boa opção quando você precisa de uma única coluna, mas pode considerar abordagens alternativas quando precisar recuperar várias colunas de uma tabela de pesquisa.