Forum Discussion
Trigger
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:
- 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)- 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())- Create a table chart and place it like this:
- 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.
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:
- 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)- 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.
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!!!
