Dax- Creating a new text column based on calculates values of 2 other columns

[Sharkybu]

Hello everyone and thank you in advance.
I have a table that consists of three columns - here is an example

Source ID	Table	Row Status
1	test	open
1	test	closed
1	prod	closed
2	test	closed
2	prod	closed
3	test	open

I want to compare the progress between the "test table" to "prod table".
So i'm trying to do a few things:

First give each 'Table' only one value in the row status; either closed or open (if it has both, it's considered open) 
Then I want to compare the the two 'Tables'  and create a new column showing the progress - cause if test is open and prod is closed then the progress is "new project", if test is closed and prod is closed then progress is "Available" and if there is only an open test then progress is  "new worker" 

When I built it in power query I used GroupBy and list.sort and list.distinct to create the values I wanted.
Which come out 5 values (prodopen;testclosed = re opened project // prodopen;testopen = Active // prodclosed;testopen = new project // prodclosed;testclosed = available for project // testopen = new researcher)

The database is already large so I'm trying to built the calculations in DAX to make it run faster.

I tried doing the same in DAX with the following code :

Researcher status =

Var filterHRFull= CALCULATETABLE(HRfull, ALLEXCEPT(HRfull,HRfull[Source ID]))

Var StatPerTable = IF(CALCULATE(DISTINCTCOUNT(HRfull[Row status]), ALLEXCEPT(HRfull,HRfull[Source ID],HRfull[Table]))=1, HRfull[Row status], "Open")

VAR StatAndTable = HRfull[Table] & StatPerTable

Var Combined =  CONCATENATEX(filterHRFull, StatAndTable, ";")

RETURN

IF(Combined = "testOpen" , "New resrarcher",....)

 But the result I'm getting contains duplicates: Source ID 1 gets the value testopen;testopen;prodclosed
Thank you.

[rajendraongole1 · Accepted solution]

Hi @Sharkybu - you can create a Calculated Column for Consolidated for status

Consolidated Status =
IF(
COUNTROWS(FILTER(HRfull, HRfull[Source ID] = EARLIER(HRfull[Source ID]) && HRfull[Table] = EARLIER(HRfull[Table]) && HRfull[Row Status] = "open")) > 0,
"open",
"closed"
)

Create another calculated column for the combined status of test and prod

Combined Status =
VAR TestStatus = MAXX(FILTER(HRfull, HRfull[Source ID] = EARLIER(HRfull[Source ID]) && HRfull[Table] = "test"), HRfull[Consolidated Status])
VAR ProdStatus = MAXX(FILTER(HRfull, HRfull[Source ID] = EARLIER(HRfull[Source ID]) && HRfull[Table] = "prod"), HRfull[Consolidated Status])
RETURN
CONCATENATE(TestStatus, ";") & ProdStatus

create another calculated column for the progress classification using switch:

Progress =
SWITCH(
[Combined Status],
"open;open", "Active",
"closed;open", "Reopened project",
"open;closed", "New project",
"closed;closed", "Available for project",
"open;", "New researcher",
BLANK()
)

Hope this approach avoids duplicates and calculates progress dynamically in your Power BI report

[Sharkybu]

Thank you, that is exactly what I was trying to get.