Link future en historic actions

[frankhofmans]

hi all,

i have the following question:

i have a database with planned consultations with corporate physicians. I want to show the future consultations in a table, but i want a alert if a certain consultation already happened in the past or if 2 identical consultations are planned for the future. For example:

Client ID	Date	Consultation name
112	01-11-2020	Intake
117	03-11-2020	Intake
119	10-11-2020	Intake
112	15-11-2020	Follow up consultation
117	18-11-2020	Reintegration consultation
133	10-12-2020	Follow up consultation
119	12-12-2020	Intake
133	15-12-2020	Follow up consultation
133	18-12-2020	Reintegration consultation

In the results i want the client ID's with the future consultations and a Yes/No if this consultation already happened or is double planned in the future, so:

Client ID	Consultation name	Date planned	Already happened in past?	Double planned in future?
119	Intake	12-12-2020	Yes	No
133	Follow up consultation	15-12-2020	No	Yes

Can someone help me out with this questions,

Many thanks in advance,

Regards, 

Frank

[V-lianl-msft · Accepted solution]

Hi @frankhofmans ,

There's no doubt that you need to create a rank column or measure to determine if the name has ever appeared before.

Column =
RANKX (
    FILTER (
        'Table',
        EARLIER ( 'Table'[Client ID] ) = 'Table'[Client ID]
            && EARLIER ( 'Table'[Consultation name] ) = 'Table'[Consultation name]
    ),
    'Table'[Date],
    ,
    ASC,
    SKIP
)

"Already happened in past?"	"Double planned in future?"

I don't know what the calculation logic is in these two columns. According to my understanding, I created the following measure. You can rewrite this measure according to your own logic.

Already happened in past =
VAR max_col =
    CALCULATE (
        MAX ( 'Table'[Column] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Client ID], 'Table'[Consultation name] ),
            'Table'[Column] > 1
        )
    )
RETURN
    IF ( max_col = MAX ( 'Table'[Column] ), "YES" )

Sample .pbix

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