Table with Conditional Calculated Column from another Table

[Syndicate_Admin]

Hello!

I need to create a newtable ( TABLE2), which has unique clients filtered from another table(TABLE1).

The main TABLE1 contains the following columns:

Date	Client	State	Code	Comment	Executive Number
21-01-2021	502	Success	1789	CUSTOMER PAYS FULL DEBT	1353
07-01-2021	502	Failure	1789	DOES NOT SUBSCRIBE TO CONVENTION	474
21-01-2021	4502	Failure	1600	WRONG FONO	608
30-12-2020	28502	Success	1788	CUSTOMER PAYS TO DEBT	818
13-01-2021	50214	Failure	1787	DOES NOT SUBSCRIBE TO CONVENTION	555

As you can see, in this table, in the CUSTOMER column, there are two customers, with different order states.

For this, I created a new table(TABLE 2)that so far contains a column with different clients,

I mean:

Client

502

4502

28502

50214

What I want to do is that to each client I want to assign the STATUS of TABLE1 as follows: if the client has two associated states (one SUCCESS and one FAILURE, such as CLIENT 502), I want the SUCCESS state to be prioritized (i.e., do not take into account their FAILURE status), which would remain in this case:

EXAMPLE 1

Client	State
502	Success
4502	Failure
28502	Success
50214	Failure

EXAMPLE 2

on the other hand if the client becomes associated with two states (FAILURE AND FAILURE, or EXIT AND EXITO) the last of the date made is prioritized.

EXAMPLE TABLE (TABLE1 BUT WITH OTHER ROWS)

Date	Client	State	Code	Comment	Executive Number
13-01-2021	25	Success	1602	CUSTOMER SUBSCRIBES AGREEMENT	1120
11-01-2021	90	FAILURE -DOES NOT LIVE IN PROPERTY	1601	DOESN'T LIVE ON THE PROPERTY	608
07-01-2021	90	Failure	1601	DOESN'T LIVE IN PROPERTY	608

this would remain in TABLE2 as:

Client	State
25	Success
90	FAILURE- DOES NOT LIVE IN PROPERTY

I hope to have been clear, I will be attentive to your comments.

Thank you!

[parry2k · Accepted solution]

@Syndicate_Admin here is the DAX expression for the calculated table that will take care of it.

Client = 
SUMMARIZE ( 
    'Client Data', 
    'Client Data'[Client],
    "State",
    COALESCE ( 
        CALCULATE ( MIN ( 'Client Data'[State] ), 'Client Data'[State] = "Success" ),
        CALCULATE ( MIN ( 'Client Data'[State] ), TOPN ( 1, ALL ( 'Client Data'[State] ), CALCULATE ( MAX ( 'Client Data'[Date] ) ), DESC ) )
    )
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡