Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Identify System Approved data point based on client ID

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. 

 

IDStatusFlag
1Authorised0
1System Approved1
23Authorised0
23System Approved1
34Authorised0
34Pipeline0
45Rejected0
64Pipeline0
56Pipeline0
Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi Anonymous 
    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Excellent, many thanks

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi Anonymous 
    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.