Forum Discussion

djalmajr's avatar
djalmajr
Regular Visitor
5 years ago
Solved

Trigger

Pessoal, tenho uma tabela onde eu preciso marcar a data da inclusão, exclusão e alteração de tipo e plano de um contrato.

Podemos criar uma tabela resumo com essas informações, mais ou menos como vemos abaixo, só que os contratos que não tiverem alteração alguma entre as datas não fariam parte do banco de dados.

Exemplo:

DATA BASECONTRATOTIPOPLANOSTATUS
01/12/20200001AZ 
01/12/20200002BQContratação
01/12/20200003DLK 
01/12/20200004YH 
08/12/20200001AZ 
08/12/20200002BQ 
08/12/20200003DLKCancelamento
08/12/20200004YH 
15/12/20200001AZ 
15/12/20200002BTAlteração de PLANO
15/12/20200004YH 
22/12/20200001RZAlteração de TIPO
22/12/20200002BT 
22/12/20200004YH 

OBS:
Essa rotina seria rodada semanalmente, então:
Contrato 0001: ele muda de TIPO no dia 22/12
Contrato 0002: ele surge no dia 01/12, porque na semana anterior não constava da base e altera o plano no dia 15/12
Contrato 0003: ao rodar a rotina no dia 08/12 ele não se encontrava mais na base, então, temos que marcar qual foi o ultimo período que ele foi encontrado.
Contrato 0004: sem alterações entre os períodos. 
Agradeço desde já a ajuda!

  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, djalmajr 

    According to your description, now I can compeletely understand your requirement, you want to get the special Status of some row of data and show the detail of the status, I have tried my best to try to achieve your needs, you can take a look at my steps and check if it’s useful:

    1. Create these calculated columns:
    rank = RANKX('Table','Table'[DATA BASE],,ASC,Dense)
    Type flag =
 var _lastTIPO=
CALCULATE(MAX('Table'[TIPO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))
return
IF(
    [rank]<>1,
IF([TIPO]=_lastTIPO,1,0),1)
    Plan flag =
var _lastPLANO=
CALCULATE(MAX('Table'[PLANO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))
return
IF(
    [rank]<>1,
IF([PLANO]=_lastPLANO,1,0),1)

     

    1. Create these measures:
    Count group by CONTRATO =

RANKX(FILTER(ALLSELECTED('Table'),[CONTRATO]=MAX([CONTRATO])),CALCULATE(MAX('Table'[DATA BASE])),,ASC,Dense )
    STATUS =
var _currentmax=
MAXX(FILTER(ALL('Table'),[CONTRATO]=MAX('Table'[CONTRATO])),[Count group by CONTRATO])
var _allmax=
MAXX(ALL('Table'),[rank])
return
SWITCH(
    TRUE(),
    [Count group by CONTRATO]=1&&MAX('Table'[rank])<>1,"New contract appears" ,
    MAX('Table'[Type flag])=0&&[Count group by CONTRATO]<>1,"Type has changed",
    MAX('Table'[Plan flag])=0&&[Count group by CONTRATO]<>1,"Plan has changed",
    [Count group by CONTRATO]=_currentmax&&[Count group by CONTRATO]<_allmax&&[Count group by CONTRATO]<>1,"Has been cancelled",
    BLANK())

     

    1. Create a table chart and place it like this:

     

    1. You can also create a calculated table to show the data with special statuses, like this:
    Table 2 =

FILTER('Table',[STATUS]<>BLANK())

     

    And you can get what you want.

    It’s hard to get the status using calculated columns so that this is the best I can do.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, djalmajr 

    According to your description, now I can compeletely understand your requirement, you want to get the special Status of some row of data and show the detail of the status, I have tried my best to try to achieve your needs, you can take a look at my steps and check if it’s useful:

    1. Create these calculated columns:
    rank = RANKX('Table','Table'[DATA BASE],,ASC,Dense)
    Type flag =
 var _lastTIPO=
CALCULATE(MAX('Table'[TIPO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))
return
IF(
    [rank]<>1,
IF([TIPO]=_lastTIPO,1,0),1)
    Plan flag =
var _lastPLANO=
CALCULATE(MAX('Table'[PLANO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))
return
IF(
    [rank]<>1,
IF([PLANO]=_lastPLANO,1,0),1)

     

    1. Create these measures:
    Count group by CONTRATO =

RANKX(FILTER(ALLSELECTED('Table'),[CONTRATO]=MAX([CONTRATO])),CALCULATE(MAX('Table'[DATA BASE])),,ASC,Dense )
    STATUS =
var _currentmax=
MAXX(FILTER(ALL('Table'),[CONTRATO]=MAX('Table'[CONTRATO])),[Count group by CONTRATO])
var _allmax=
MAXX(ALL('Table'),[rank])
return
SWITCH(
    TRUE(),
    [Count group by CONTRATO]=1&&MAX('Table'[rank])<>1,"New contract appears" ,
    MAX('Table'[Type flag])=0&&[Count group by CONTRATO]<>1,"Type has changed",
    MAX('Table'[Plan flag])=0&&[Count group by CONTRATO]<>1,"Plan has changed",
    [Count group by CONTRATO]=_currentmax&&[Count group by CONTRATO]<_allmax&&[Count group by CONTRATO]<>1,"Has been cancelled",
    BLANK())

     

    1. Create a table chart and place it like this:

     

    1. You can also create a calculated table to show the data with special statuses, like this:
    Table 2 =

FILTER('Table',[STATUS]<>BLANK())

     

    And you can get what you want.

    It’s hard to get the status using calculated columns so that this is the best I can do.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • djalmajr's avatar
      djalmajr
      Regular Visitor
      5 years ago

      Oi, v-robertq-msft , primeiramente muito obrigado por me ajudar!

      Voce entendeu perfeitamtente minha necessidade!

      Contudo tive que fazer alguns ajustes nas fórmulas.

      As formulas não apresentaram erros contudo não consegui o mesmo resultado que voce, obviamente fiz alguma coisa errada.

      Vou enviar o arquivo .pbix.

      Segue uma legenda dos ajustes nos nomes das colunas:

      Table = fPool

      Tipo = Tipo

      Plano = Plano (Nome exato do portal)

      Contrato = Numero

      Count group by CONTRATO = Contagem Massa

      Status = Situação

      Flag type = Flag Tipo

      Flag Plan = Flag Plano

      Mais uma vez muito obrigado!

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    djalmajr ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, djalmajr 

    According to your description, I have two understandings, you can check what's your true meaning:

    First: You only need to get a table that contains the status update information, you can achieve this through the calculated table:

    Table 2 =

FILTER('Table',[STATUS]<>BLANK())

     

    Second: You want to use the DAX statement to automatically determine the status update and filter out a table. In this way, I can only use DAX to determine the updates of [TYPE] and {PLAN}, because other status change is not very logical, you can take a look at my method and find if it’s useful:

    1. Create two calculated columns in the main table:
    rank = RANKX('Table','Table'[DATA BASE],,ASC,Dense)
    Flag =

var _lastTIPO=

CALCULATE(MAX('Table'[TIPO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))

var _lastPLANO=

CALCULATE(MAX('Table'[PLANO]),FILTER('Table',[CONTRATO]=EARLIER([CONTRATO])&&[rank]=EARLIER([rank])-1 ))

return

IF(

    [rank]<>1,

IF([TIPO]=_lastTIPO&&[PLANO]=_lastPLANO,1,0),1)
    1. Create a calculated table:
    Table 3 =

FILTER('Table',[Flag]=0)

    And I can get a table like this:

     

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • djalmajr's avatar
      djalmajr
      Regular Visitor
      5 years ago

      Oi, v-robertq-msft , obrigado por me ajudar!

      Acontece que eu acabei confundindo voces, na minha base não tem a coluna "Status", eu preciso criar essa informação.

      Deixa eu ver se melhoro a descrição do meu problema:

      1) Eu preciso saber quando um contrato tiver alterado a informação de Tipo e/ou o Plano e marcar a data onde isso ocorreu.

      2) Quando for cancelado e quando um novo contrato surgir e marcar a data onde isso ocorreu.

      Mais uma vez muito obrigado!!!

       

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    Hi, djalmajr 

    I’ve double-checked my solution and find some bug in it, I tried to open the link you gave but I don’t have access to it, you can take a look:

    Then I opened my original test pbix file, and changed my solution like this, you can follow my steps:

    1. Create these calculated columns:
    rank =

RANKX('Table','Table'[DATA BASE],,ASC,Dense)
    Plan flag =

var _lastPLAN=

CALCULATE(

    MAX('Table'[PLAN]),

    FILTER('Table',

    [CONTRACT]=EARLIER([CONTRACT])&&

    [rank]=EARLIER([rank])-1 ))

return

IF(

    [rank]<>1,

IF([PLAN]=_lastPLAN,0,1)

,0)
    Type flag =

var _lastTYPE=

CALCULATE(

    MAX('Table'[TYPE]),

    FILTER('Table',

    [CONTRACT]=EARLIER([CONTRACT])&&

    [rank]=EARLIER([rank])-1 ))

return

IF(

    [rank]<>1,

IF([TYPE]=_lastTYPE,0,1)

,0)
    Cancel flag =

var _maxrank=

MAXX(ALLSELECTED('Table'),[rank])

var _contractsnextday=

SELECTCOLUMNS(

    FILTER('Table',

    [rank]=EARLIER([rank])+1),

    "Contract",[CONTRACT])

return

IF(

    [rank]<_maxrank,

IF(

    [CONTRACT] in _contractsnextday,

    0,1)

    ,0)
    1. Create these measures:
    Rank group by CONTRACT =

RANKX(

    FILTER(ALL('Table'),

    [CONTRACT]=MAX([CONTRACT])),

CALCULATE(MAX('Table'[DATA BASE])),,ASC,Dense )
    Status Change =

SWITCH(

    TRUE(),

    [Rank group by CONTRACT]=1,"New contract appears" ,

    MAX('Table'[Type flag])=1&&MAX('Table'[Plan flag])=1,"Plan and type has changed",

    MAX('Table'[Type flag])=1,"Type has changed",

    MAX('Table'[Plan flag])=1,"Plan has changed",

    MAX('Table'[Cancel flag])=1,"Has been cancelled",

    BLANK())
    1. Create a Table chart and some Slicers like this:

     

    And you can get what you want.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.