Identify System Approved data point based on client ID

[Invesco]

Hi Experts

See table below

I am trying to identify all data points in my FACT Table where we have clients that have the same ID and have Status as Authorised and System Apporved, so ID 1 has Status of Authorised and System Apporved in new column add 1 against System Apporved as shown else 0. Likewise ID 23. 

ID	Status	Flag
1	Authorised	0
1	System Approved	1
23	Authorised	0
23	System Approved	1
34	Authorised	0
34	Pipeline	0
45	Rejected	0
64	Pipeline	0
56	Pipeline	0

[Invesco]

Excellent, many thanks

[Ritaf1983]

Happy to help🎀

[Ritaf1983 · Accepted solution]

Hi @Invesco 
You can created calculated column with the DAX :

Has_Authorised =

IF (

    'Table'[Status] = "System Approved",

    IF (

        COUNTROWS (

            FILTER (

                'Table',

                'Table'[ID] = EARLIER('Table'[ID]) &&

                'Table'[Status] = "Authorised"

            )

        ) > 0,

        1,

        0

    ),

   0

)

PBIX is attached

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