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
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:
- 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)- 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())- 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.